Dax to select last value for user in time series table

[ChrisWilliams]

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

is shown below.

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.

[Vvelarde]

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

[Oladapo]

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.

[ChrisWilliams]

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

[RicardoXavier]

I have the same problem.

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

[Vvelarde]

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)

[KGrice]

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.