DAX - Get the latest value in the week

[milomilo2020]

Hi!

I have this table with Weeks (First Day of Week), Dates and a  DAX Measure

And I want to modify the Measure in order to have a chart and use the Week dimension on it, then show the latest value of each Week, like this:

Thanks

[Icey · Accepted solution]

Hi @milomilo2020 ,

How about this?

Latest Value by Week = 
VAR LatestDatebyWeek_ =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Week] ) )
RETURN
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Date] = LatestDatebyWeek_ )

Modified Latest Value by Week =
SUMX ( VALUES ( 'Table'[Week] ), [Latest Value by Week] )

Best Regards,

Icey

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

[milomilo2020]

@ribisht17  hi , thanks , but I think I didnt explain well my requeriment:

I need a line chart displaying

X Axis = Week Column

Measure = Only the value of the LAST day of the Week

For example for Week 15/05/2022 the value needs to be 1627

[ribisht17]

@milomilo2020 

Rank= RANKX(FILTER(all('Table'),'Rank'[Week]=max('Rank'[Week])),'Rank'[date],,DESC,Dense)

Now Just Filter RANK=1

Regards,

Ritesh

[ribisht17]

This should work

Measure=

if(

Rank= RANKX(FILTER(all('Table'),'Rank'[First of Week]=max('Rank'[First of Week])),'Rank'[date],,DESC,Dense)=1,fact,"")

X Axis = First of Week

Regards,

Ritesh