topic Measure based on latest date in DAX Commands and Tips

Measure based on latest date

M_SBS_6 — Wed, 10 Jul 2024 10:06:46 GMT

Hi,

I have a date and value column within my table.

I have a card and within that, I need the value based on the latest date. I could have 5 rows of data linked to one date so need to sum them up.

Example below, my card would return a value of 1800.

Date. Desc. Value

10/07/2024 test1. 1000

10/07/2024. Test2. 800

09/07/2024 test1. 600

09/07/2024. Test 2. 300

Re: Measure based on latest date

rajendraongole1 — Wed, 10 Jul 2024 10:14:14 GMT

Hi M_SBS_6 - can you try below measure to get the data as per latest date

Measure used:

Latest Date Value =

VAR LatestDate = MAX('Latest'[Date])

RETURN

CALCULATE(

SUM('Latest'[Value]),

'Latest'[Date] = LatestDate

)

It works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Re: Measure based on latest date

AnkitaaMishra — Wed, 10 Jul 2024 10:24:47 GMT

Hi M_SBS_6 ,

please try below DAX :

Total_for_Max_Date =

CALCULATE(

SUM(Example[Value]),

FILTER(

Example,

Example[Date] = MAX(Example[Date])

)

If this works for you, please accept it as solution.

Thanks,

Ankita

Re: Measure based on latest date

manvishah17 — Wed, 10 Jul 2024 10:33:12 GMT

Hi M_SBS_6 ,
You can use either measure or calculated column ,
Measure :

LatestDateValueSum = CALCULATE( SUM('Table'[Value]), FILTER( 'Table', 'Table'[Date] = MAX('Table'[Date]) )

or
Create a calculated column to rank the dates so you can identify the latest date:

DateRank =
RANKX(
ALL('Table'[Date]),
'Table'[Date],
,
DESC,
DENSE
)

Create a measure to sum the values for the latest date by filtering the data based on the rank:

LatestDateValueSum =
CALCULATE(
SUM('Table'[Value]),
FILTER(
'Table',
'Table'[DateRank] = 1
)
)