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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChrisWilliams
Advocate II
Advocate 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

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

7 REPLIES 7
Vvelarde
Community Champion
Community Champion

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

Hi @Vvelarde,

The maximum value isnt necessarily the latest entry value per user. You made a good trial but not quite perfect, i think the power query route is still the best approach to handle it.

Cheers.

@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.

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

I have the same problem.

 

I used this suggestion to my table, but it didn't work 

 

RicardoXavier_0-1627508816867.png

 

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
KGrice
Memorable Member
Memorable Member

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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