Show value from the latest month in each month row

[Anonymous]

Hi, I'm trying to produce a measure called the Most Recent Budget that gets its value from the value available from the latest fiscal month with budget data in the fiscal year.

For example,
Row with A and C in 2021 will have 12 as its latest fiscal month (Jun) and hence the most recent budget value is 100 (from month 12).
Row with A and C in 2022 will have 3 as its latest fiscal month (Sep) and hence the most recent budget value is 20
Row with B and C in 2022 will have 3 as its latest fiscal month and hence the most recent budget is 60

Dimensions 1, 2, and Dim Date all have a 1 to many relationship with the fact table.
What's the best DAX to get this result?

Here's my sample data to help clarify my question:

Column from 1st Dimension	Column from 2nd Dimension	Fiscal Year from DimDate	Fiscal Month from DimDate	Budget	Most Recent Budget
A	D	2021	1	10	30
A	D	2021	2	10	30
A	D	2021	3	10	30
A	D	2021	4	10	30
A	D	2021	5	10	30
A	D	2021	6	10	30
A	D	2021	7	20	30
A	D	2021	8	20	30
A	D	2021	9	20	30
A	D	2021	10	20	30
A	D	2021	11	20	30
A	D	2021	12	30	30
A	C	2021	1	50	100
A	C	2021	2	50	100
A	C	2021	3	50	100
A	C	2021	4	50	100
A	C	2021	5	50	100
A	C	2021	6	50	100
A	C	2021	7	50	100
A	C	2021	8	50	100
A	C	2021	9	50	100
A	C	2021	10	50	100
A	C	2021	11	50	100
A	C	2021	12	100	100
A	D	2022	1	20	50
A	D	2022	2	20	50
A	D	2022	3	50	50
A	C	2022	1	10	20
A	C	2022	2	10	20
A	C	2022	3	20	20
B	C	2022	1	70	60
B	C	2022	2	70	60
B	C	2022	3	60	60

Please let me know if my question is unclear.

Thanks a lot in advance

[Anonymous]

@tamerj1 thanks for your reply. I noticed that the latest month's value for the same group of Column1, 2, and year is not showing in each previous month. From your file and snapshot, I highlighted below where the 100 should be showing as well, which is 100 in each month 1 to 12. Currently on your solution, it's only showing 100 in month 12.
They should all show 100 because they share the same Col 1, 2, and Year. I want it to look at what's the latest month in the fiscal year that has data (in this case month 12, and then show that value in any month for A, C, and 2021)
I hope that makes it clearer. Sorry if it wasn't clear before.

[tamerj1 · Accepted solution]

@Anonymous 
Guilty of not paying much attention before submitting the answer. Actually the solution was working correctly before I realized that the total was showing wrong value. Here is my original solution with wrong total. 

So I tried to fix it using the formula which I have posted in my previous reply but I did not notice that by doing that I lost the filter removing effect of ALLSELECTED and that I should have used ALL instead.

So here is the correct solution with the correct total https://www.dropbox.com/t/29wWLIQvHbMxc7Bn

Most Recent Budget (Correct Total) = 
SUMX (
    CROSSJOIN (
        SUMMARIZE ( 'Date', 'Date'[Fiscal Year], 'Date'[Fiscal Month] ),
        VALUES ( Dim1[Column1] ),
        VALUES ( Dim2[Column2] )
    ),
    CALCULATE (
        VAR MostRecentMonth = 
            CALCULATE ( 
                MAX ( 'Date'[Fiscal Month] ),
                ALL ( 'Date'[Fiscal Month] ),
                CROSSFILTER ( 'Date'[Date], 'Fact'[Date], Both )
            )
        RETURN
            CALCULATE ( 
                SUM ( 'Fact'[Budget] ),
                'Date'[Fiscal Month] = MostRecentMonth
            )
    )
)

[Anonymous]

Thank you @tamerj1 the solutions work. I end up using the one with the wrong total because:
1. I don't need to show the subtotal at all, just to show the latest value in each month row
2. With the correct total option, I believe the DAX formula will need to be updated if we have a 3rd column, correct? So, for example, would the first part of the code needs to become:

CROSSJOIN (

        SUMMARIZE ( 'Date', 'Date'[Fiscal Year], 'Date'[Fiscal Month] ),

        VALUES ( Dim1[Column1] ),

        VALUES ( Dim2[Column2] ),
        VALUES ( Dim3[Column3] ) )
   I was thinking if this is how the formula behaves, that is, we need to know the exact level of summarisation whereas the one with the wrong total is more flexible?

3. I also adjusted the one with the wrong total slightly from ALLSELECTED to ALL

This is because when we filter the fiscal month to, say, month 5, we will see the wrong latest number because of the filter context, is this right? I do need to (sometimes) filter the month by single selection only, for example just to month 5.

The magic of the DAX you created seems to be in the CROSSFILTER Both. Can you please explain how exactly this works in this case? Is it because the fact table date filters the dim table date?

Thank you so much @tamerj1 

[tamerj1]

@Anonymous 
Yes because the MAX is required only for the dates that are available in the fact table. This is why the fact has to filter the date otherwise the MAX will allways returnDecember in all cases. 

[tamerj1]

Hi @Anonymous 
Please refer to sample file with the solution https://www.dropbox.com/t/0Qmz5da7Kh2Vufvd

Most Recent Budget = 
SUMX (
    CROSSJOIN (
        SUMMARIZE ( 'Date', 'Date'[Fiscal Year], 'Date'[Fiscal Month] ),
        VALUES ( Dim1[Column1] ),
        VALUES ( Dim2[Column2] )
    ),
    CALCULATE (
        VAR MostRecentMonth = 
            CALCULATE ( 
                MAX ( 'Date'[Fiscal Month] ),
                ALLSELECTED ( 'Date'[Fiscal Month] ),
                CROSSFILTER ( 'Date'[Date], 'Fact'[Date], Both )
            )
        RETURN
            CALCULATE ( 
                SUM ( 'Fact'[Budget] ),
                'Date'[Fiscal Month] = MostRecentMonth
            )
    )
)