Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
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 DimensionColumn from 2nd DimensionFiscal Year from DimDateFiscal Month from DimDateBudgetMost Recent Budget
AD202111030
AD202121030
AD202131030
AD202141030
AD202151030
AD202161030
AD202172030
AD202182030
AD202192030
AD2021102030
AD2021112030
AD2021123030
AC2021150100
AC2021250100
AC2021350100
AC2021450100
AC2021550100
AC2021650100
AC2021750100
AC2021850100
AC2021950100
AC20211050100
AC20211150100
AC202112100100
AD202212050
AD202222050
AD202235050
AC202211020
AC202221020
AC202232020
BC202217060
BC202227060
BC202236060


Please let me know if my question is unclear.

Thanks a lot in advance

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

1.png2.png

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

3.png4.png

 

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

 

View solution in original post

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.

HiCrackrz_0-1658108246863.png

 

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

1.png2.png

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

3.png4.png

 

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
HiCrackrz_1-1658143024270.png

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.

HiCrackrz_2-1658143189761.png

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 

 

 

@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
Super User
Super User

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

2.png1.png

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors