topic Re: How to write a DAX to get category for most recent date for each account with cumulative value in DAX Commands and Tips

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

rob_vander2 — Mon, 20 Jan 2025 11:58:15 GMT

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?

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

Poojara_D12 — Mon, 20 Jan 2025 12:45:36 GMT

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.

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

ThxAlot — Mon, 20 Jan 2025 13:27:00 GMT

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

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

rob_vander2 — Mon, 20 Jan 2025 13:52:14 GMT

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.

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

rob_vander2 — Mon, 20 Jan 2025 14:08:19 GMT

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

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

johnt75 — Mon, 20 Jan 2025 14:38:41 GMT

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

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

Anonymous — Tue, 21 Jan 2025 02:12:08 GMT

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

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

rob_vander2 — Thu, 23 Jan 2025 10:41:47 GMT

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

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

johnt75 — Thu, 23 Jan 2025 11:02:33 GMT

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.

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

rob_vander2 — Thu, 23 Jan 2025 16:26:34 GMT

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