Reporting full year forecast with changing forecast on different journal type

mira_abel

New member
Joined
Jul 21, 2020
Messages
1
Reaction score
0
Points
0
Excel Version(s)
Microsoft Office 365 ProPlus 3
Hello, dear DAX and Power Pivot enthusiasts!

I am just learning DAX measures and Power Pivot... I have been trying to solve a reporting challenge on my own but I think I need your sharp minds and a piece of advice.

I have a lookup table for journal types.

Journal TypeJournal NameJournal UseComments
forecast for
periods
ACCRAccrualsActual
GJGeneralActual
EXPSEmployee ExpensesActual
YB19Budget 2019Budget 2019
YB20Budget 2020Budget 2020
MRF02Forecast1+11 ForecastFeb-Dec
MRF03Forecast2+10 ForecastMar-Dec
MRF04Forecast3+9 ForecastApr-Dec
MRF05Forecast4+8 ForecastMay-Dec
MRF06Forecast5+7 ForecastJun-Dec
MRF07Forecast6+6 ForecastJul-Dec
MRF08Forecast7+5 ForecastAug-Dec
MRF09Forecast8+9 ForecastSep-Dec

1. Actual journal types are stable year on year and month on month.
2. Budget journal types are stable month on month but change every year. In 2019 we have a budget posted with a journal type YB19 for each month of three year window - 2019-2020-2021, in 2020 we have a new budget for each month of 2020-2021-2023 posted with a journal type YB20
3. Forecast journals are changing every month, e.g. in Jan-2019 we posted a MRF02 journal type for each month of Feb-Dec 2019 and Jan-Dec 2020-2021, In July 2019 we posted a MRF08 jouranl type for each month of Aug-Dec 2019 and Jan-Dec 2020-2021. To differentiate between the years, we have a parameter "Budget Year" in the Transaction Data Table against forecast and budget year journal types.

Transaction data table will look like
LedgerAccount CodeAccounting PeriodCurrency CodeTransaction AmountJournal TypeBudget Year
A9999992019/001USD 120,538.00ACCRnull
A9999992019/002USD 120,638.00GJnull
A9999992019/003USD 120,738.00EXPSnull
A9999992019/004USD 150,000.00ACCRnull
A9999992019/005USD 150,100.00GJnull
A9999992019/006USD 150,200.00ACCRnull
A9999992019/007USD 150,300.00EXPSnull
A9999992019/008USD 150,250.00EXPSnull
F9999992019/008USD 141,592.10MRF082019
F9999992019/009USD 141,602.10MRF082019
F9999992019/010USD 141,612.10MRF082019
F9999992019/011USD 141,622.10MRF082019
F9999992019/012USD 141,632.10MRF082019
F9999992019/009USD 148,713.71MRF092019
F9999992019/010USD 156,149.39MRF092019
F9999992019/011USD 163,956.86MRF092019
F9999992019/012USD 172,154.70MRF092019
B9999992019/001USD 141,592.10YB192019
B9999992019/002USD 141,792.10YB192019
B9999992019/003USD 141,642.10YB192019
B9999992019/004USD 141,842.10YB192019
B9999992019/005USD 141,692.10YB192019
B9999992019/006USD 141,892.10YB192019
B9999992019/007USD 141,742.10YB192019
B9999992019/008USD 141,942.10YB192019
B9999992019/009USD 141,792.10YB192019
B9999992019/010USD 141,992.10YB192019
B9999992019/011USD 141,842.10YB192019
B9999992019/012USD 142,042.10YB192019

So I have to come up with a solution to work for reporting for any month like follows:

Rep monthJul-19
Act YTDBud YTDFul Year ForecastBudget Full Year
962,514.00 992,194.70 1,670,574.50 1,701,805.20
Journal UseActual Jan-JulBudget YB19 journal type for Jan-JulActual Jan-Jul plus MRF08 for Aug-DecBudget YB19 for Jan-Dec

Rep monthAug-19
Act YTDBud YTDFul Year ForecastBudget Full Year
1,112,764.00 1,134,136.80 1,753,738.66 1,701,805.20
Journal UseActual Jan-AugBudget YB19 journal type for Jan-AugActual Jan-Aug plus MRF09 for Sep-DecBudget YB19 for Jan-Dec
 
Top