Dear Mr. Excel community and DAX 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.
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
So I have to come up with a solution to work for reporting for any month like follows:
Could you please help?
Thank you very much in advance
Mira
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 |
Could you please help?
Thank you very much in advance
Mira
