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