cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Show value from the latest month in each month row

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.

1 ACCEPTED SOLUTION
Super User

@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
)
)
)``````

5 REPLIES 5
Anonymous
Not applicable

@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.

Super User

@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
Not applicable

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

Super User

@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.

Super User

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
)
)
)``````

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors