PDA

View Full Version : Reporting full year forecast with changing forecast on different journal type



mira_abel
2020-07-21, 07:54 PM
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 Type
Journal Name
Journal Use
Comments
forecast for
periods


ACCR
Accruals
Actual



GJ
General
Actual



EXPS
Employee Expenses
Actual



YB19
Budget 2019
Budget 2019



YB20
Budget 2020
Budget 2020



MRF02
Forecast
1+11 Forecast
Feb-Dec


MRF03
Forecast
2+10 Forecast
Mar-Dec


MRF04
Forecast
3+9 Forecast
Apr-Dec


MRF05
Forecast
4+8 Forecast
May-Dec


MRF06
Forecast
5+7 Forecast
Jun-Dec


MRF07
Forecast
6+6 Forecast
Jul-Dec


MRF08
Forecast
7+5 Forecast
Aug-Dec


MRF09
Forecast
8+9 Forecast
Sep-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


Ledger
Account Code
Accounting Period
Currency Code
Transaction Amount
Journal Type
Budget Year


A
999999
2019/001
USD
120,538.00
ACCR
null


A
999999
2019/002
USD
120,638.00
GJ
null


A
999999
2019/003
USD
120,738.00
EXPS
null


A
999999
2019/004
USD
150,000.00
ACCR
null


A
999999
2019/005
USD
150,100.00
GJ
null


A
999999
2019/006
USD
150,200.00
ACCR
null


A
999999
2019/007
USD
150,300.00
EXPS
null


A
999999
2019/008
USD
150,250.00
EXPS
null


F
999999
2019/008
USD
141,592.10
MRF08
2019


F
999999
2019/009
USD
141,602.10
MRF08
2019


F
999999
2019/010
USD
141,612.10
MRF08
2019


F
999999
2019/011
USD
141,622.10
MRF08
2019


F
999999
2019/012
USD
141,632.10
MRF08
2019


F
999999
2019/009
USD
148,713.71
MRF09
2019


F
999999
2019/010
USD
156,149.39
MRF09
2019


F
999999
2019/011
USD
163,956.86
MRF09
2019


F
999999
2019/012
USD
172,154.70
MRF09
2019


B
999999
2019/001
USD
141,592.10
YB19
2019


B
999999
2019/002
USD
141,792.10
YB19
2019


B
999999
2019/003
USD
141,642.10
YB19
2019


B
999999
2019/004
USD
141,842.10
YB19
2019


B
999999
2019/005
USD
141,692.10
YB19
2019


B
999999
2019/006
USD
141,892.10
YB19
2019


B
999999
2019/007
USD
141,742.10
YB19
2019


B
999999
2019/008
USD
141,942.10
YB19
2019


B
999999
2019/009
USD
141,792.10
YB19
2019


B
999999
2019/010
USD
141,992.10
YB19
2019


B
999999
2019/011
USD
141,842.10
YB19
2019


B
999999
2019/012
USD
142,042.10
YB19
2019



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



Rep month
Jul-19






Act YTD
Bud YTD
Ful Year Forecast
Budget Full Year



962,514.00
992,194.70
1,670,574.50
1,701,805.20


Journal Use
Actual Jan-Jul
Budget YB19 journal type for Jan-Jul
Actual Jan-Jul plus MRF08 for Aug-Dec
Budget YB19 for Jan-Dec





Rep month
Aug-19






Act YTD
Bud YTD
Ful Year Forecast
Budget Full Year



1,112,764.00
1,134,136.80
1,753,738.66
1,701,805.20


Journal Use
Actual Jan-Aug
Budget YB19 journal type for Jan-Aug
Actual Jan-Aug plus MRF09 for Sep-Dec
Budget YB19 for Jan-Dec