How to write a DAX to get category for most recent date for each account with cumulative value

[rob_vander2]

Hi All,

I have below data set. I want to show value of Category for most recent date in the report for each account. There will be many accounts with different recent date. I want to show highlighted  record on the report, but at the same time I want values to be cummulative

Account	Category	Date	Value
1	A	01/01/2024	100
1	A	02/01/2024	50
1	B	03/01/2024	200
1	A	04/01/2024	150
1	B	05/01/2024	140
2	A	01/01/2024	200
2	B	02/01/2024	300
2	B	03/01/2024	110
2	A	04/01/2024	90

I want output as below. Highlighted in red as rows and hilghted in orange & blue as corresponding cummulative values 

Account	Category	Value
1	B	640
2	A	700

How do I write DAX to create this report?

[Anonymous · Accepted solution]

Hi @rob_vander2 ,
Thanks for their reply.
You can try the following steps

Sample data

Account	Category	Date	Value
1	A	12/28/2024	100
1	B	12/29/2024	200
1	A	12/30/2024	50
1	A	12/31/2024	150
1	B	1/1/2025	110
1	B	1/2/2025	200
1	A	1/3/2025	190
1	B	1/4/2025	50
1	A	1/5/2025	120
1	B	1/6/2025	80
1	A	1/7/2025	100
2	A	12/28/2024	20
2	B	12/29/2024	180
2	B	12/30/2024	130
2	A	12/31/2024	40
2	B	1/1/2025	30
2	A	1/2/2025	100
2	B	1/3/2025	140
2	A	1/4/2025	160
2	A	1/5/2025	200
2	B	1/6/2025	300
2	B	1/7/2025	40
2	A	1/8/2025	50
2	B	1/9/2025	140

Create a date table

Date slicer = VALUES('Table'[Date])

Create three measures

Total = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALLEXCEPT(
            'Table',
            'Table'[Account]
        ),
        'Table'[Date] <= MAX('Date slicer'[Date])
    )
)

MaxDateCategory = 
VAR MaxDatePerAccount = 
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALLEXCEPT('Table', 'Table'[Account]),
            'Table'[Date] <= MAX('Date slicer'[Date])
        )
    )
RETURN
    CALCULATE(
        FIRSTNONBLANK('Table'[Category], 1),
        'Table'[Date] = MaxDatePerAccount,
        ALLEXCEPT('Table', 'Table'[Account])
    )

IsMAXCategory = 
IF(
    SELECTEDVALUE('Table'[Category]) = [MaxDateCategory],
    1,
    0
)IsMAXCategory = 
IF(
    SELECTEDVALUE('Table'[Category]) = [MaxDateCategory],
    1,
    0
)

Appply the measure to the target table and set it as 1

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[rob_vander2]

@Anonymous @johnt75  Both of your solutions works. I would like to know which one will be better in terms of performance?

[johnt75]

The only way to find that out is to test them.

Create 2 visuals, one using each method proposed. Use Performance Analyzer to get a copy of the DAX query underpinning the visual, then open DAX Studio and enable Server Timings. Run the queries and see which is fastest.

[rob_vander2 · Accepted solution]

@Anonymous @johnt75  below DAX works fine for Latest category value

VAR max_date = max(Table[Date])

VAR latest_category = 
      CALCULATE(FIRSTNONBLANK(Table[Category],1),
                 Table[Date] = max_date)
RETURN latest_category

[Anonymous · Accepted solution]

Hi @rob_vander2 ,
Thanks for their reply.
You can try the following steps

Sample data

Account	Category	Date	Value
1	A	12/28/2024	100
1	B	12/29/2024	200
1	A	12/30/2024	50
1	A	12/31/2024	150
1	B	1/1/2025	110
1	B	1/2/2025	200
1	A	1/3/2025	190
1	B	1/4/2025	50
1	A	1/5/2025	120
1	B	1/6/2025	80
1	A	1/7/2025	100
2	A	12/28/2024	20
2	B	12/29/2024	180
2	B	12/30/2024	130
2	A	12/31/2024	40
2	B	1/1/2025	30
2	A	1/2/2025	100
2	B	1/3/2025	140
2	A	1/4/2025	160
2	A	1/5/2025	200
2	B	1/6/2025	300
2	B	1/7/2025	40
2	A	1/8/2025	50
2	B	1/9/2025	140

Create a date table

Date slicer = VALUES('Table'[Date])

Create three measures

Total = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALLEXCEPT(
            'Table',
            'Table'[Account]
        ),
        'Table'[Date] <= MAX('Date slicer'[Date])
    )
)

MaxDateCategory = 
VAR MaxDatePerAccount = 
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALLEXCEPT('Table', 'Table'[Account]),
            'Table'[Date] <= MAX('Date slicer'[Date])
        )
    )
RETURN
    CALCULATE(
        FIRSTNONBLANK('Table'[Category], 1),
        'Table'[Date] = MaxDatePerAccount,
        ALLEXCEPT('Table', 'Table'[Account])
    )

IsMAXCategory = 
IF(
    SELECTEDVALUE('Table'[Category]) = [MaxDateCategory],
    1,
    0
)IsMAXCategory = 
IF(
    SELECTEDVALUE('Table'[Category]) = [MaxDateCategory],
    1,
    0
)

Appply the measure to the target table and set it as 1

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[johnt75]

You can get the latest category with

Latest Category =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR BaseTable =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Category], 'Table'[Date] ),
        'Date'[Date] <= MaxDate
    )
VAR Result =
    SELECTCOLUMNS (
        INDEX ( 1, BaseTable, ORDERBY ( 'Table'[Date], DESC ) ),
        'Table'[Category]
    )
RETURN
    Result

and you can get the cumulative value using

Cumulative value =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Date'[Date] <= MaxDate )
RETURN
    Result

[rob_vander2]

@Poojara_D12  Thanks for your reply. Problem is I want to display category based on dynamic selection on Calendar dimensions like year. Let's say I have data from 2020 to 2024, if user select year 2023, for each account we need to check filter from 2020 to 2023 and pick latest date and perform cummulative and show corresponding category for that latest date. Hope it clarifies.

[ThxAlot]

It's annoying to see shxty ChatGPT-like answers everywhere.

[rob_vander2]

@ThxAlot  Thanks for your reply.  I want my calculation to be dynamic. Let's say I have data from 01-Jan-2020 to 31-12-2024. I have calendar where I have Year, Month Column. If user selects Year - 2023, I want to look at dates from 01-Jan-20202 to latest avauilable date in 2023 for each account which will be the cummulative value. Category values will be the value for latest available date between (01-01-2020 till selected period from calendar). Hope it clarifies

[Poojara_D12]

Hi @rob_vander2 

To achieve your desired output using DAX:

Identify Most Recent Date for Each Account:

• Create a calculated column to get the most recent date for each account:

DAX: MostRecentDate = CALCULATE(MAX('YourData'[Date]), ALLEXCEPT('YourData', 'YourData'[Account]))

Sum Values for the Most Recent Date:

• Create a measure to sum values only for the most recent date for each account:

DAX: RecentValue =CALCULATE(SUM('YourData'[Value]), 'YourData'[Date] = 'YourData'[MostRecentDate])

Calculate Cumulative Value for Each Account:

• Create a measure to calculate cumulative values up to the most recent date:

DAX: CumulativeValue =CALCULATE(SUM('YourData'[Value]),FILTER('YourData', 'YourData'[Account] = EARLIER('YourData'[Account]) && 'YourData'[Date] <= EARLIER('YourData'[Date])))

Visualize the Report:.

Display the Account, Category, and Value fields in a table and use the RecentValue and CumulativeValue measures.

Highlight the most recent date using conditional formatting.

This will give you the most recent category and cumulative values for each account.