Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
milomilo2020
Frequent Visitor

DAX - Get the latest value in the week

Hi!

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

milomilo2020_0-1654000823228.png

 

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:

milomilo2020_1-1654000876437.png

 

 

Thanks

 

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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

Icey_0-1654236577639.png

 

 

Best Regards,

Icey

 

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

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

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

Icey_0-1654236577639.png

 

 

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
Frequent Visitor

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

 

milomilo2020_1-1654005738585.png

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

@milomilo2020 

 

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

 

Now Just Filter RANK=1

 

Regards,

Ritesh

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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