cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ChrisWilliams Helper II
Helper II

Dax to select last value for user in time series table

I have a table which contains separate values per day, per user.  A simplified version of the schema and data

is shown below.

 

table.PNG

 

I'd like to return the latest value per user.  In this case, the result set would look like this.  Not all users are reported on the Max Date, so we have to go back to July 2 to get the latest value for User 1.

 

filtered.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisWilliams Helper II
Helper II

Re: Dax to select last value for user in time series table

I guess the command is something like this?  Does this seem correct?

 

MyValues= SUMMARIZE(MyTable,
MyTable[User],
"LastDate", Max(MyTable[Date]),
"ItemCount", CALCULATE((Max(MyTable[Value])),
Filter(MyTable, MyTable[Date] = Max(MyTable[Date]))))

View solution in original post

5 REPLIES 5
KGrice Solution Sage
Solution Sage

Re: Dax to select last value for user in time series table

Ken Puls has an article on what looks like the exact same scenario, keeping the most recent entry per user. I've used it before for something similar. You might check it out and see if that helps! Even though he's doing it in Power Query outside of Power BI Desktop, he's got step-by-step instructions that should cross over.

Vvelarde Community Champion
Community Champion

Re: Dax to select last value for user in time series table

hi   @ChrisWilliams

 

You can do it this with a summarize table:

 

Tabla = SUMMARIZE(MaxTable;MaxTable[User];"LastDate";Max(MaxTable[Fecha]);"LastValue";CALCULATE(max(MaxTable[Value]);FILTER(MaxTable;MaxTable[Fecha]=Max(MaxTable[Fecha]))))




Lima - Peru
ChrisWilliams Helper II
Helper II

Re: Dax to select last value for user in time series table

@Vvelarde thanks for the pointer, but I'm afraid that your DAX won't save.  I don't know what Fecha is and PowerBI is expecting commas instead of semi-colons.  I can try to work through it, but I think Max() requires two values for example and I just don't know where to take this.

ChrisWilliams Helper II
Helper II

Re: Dax to select last value for user in time series table

I guess the command is something like this?  Does this seem correct?

 

MyValues= SUMMARIZE(MyTable,
MyTable[User],
"LastDate", Max(MyTable[Date]),
"ItemCount", CALCULATE((Max(MyTable[Value])),
Filter(MyTable, MyTable[Date] = Max(MyTable[Date]))))

View solution in original post

Vvelarde Community Champion
Community Champion

Re: Dax to select last value for user in time series table

hi @ChrisWilliams Yeah this is correct.

 

Fecha is Date (Spanish translation)

 

And My Version of PowerBI work with ; instead of , . (Regional configuration of my PC)

 

 

 

 




Lima - Peru

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors