Power BI calculate sum only last value of duplicate ID

Question

I'm struggling to create a Measure that sums a column and have it filter out duplicate IDs while taking only the latest row.

For example, there is a table as such:

UID | Quantity | Status    | StatusDate
aaa | 3        | Shipped   | 11/1/2020
aaa | 3        | Delivered | 11/5/2020
bbb | 5        | Ordered   | 10/29/2020
ccc | 8        | Shipped   | 11/4/2020

So the idea would be to sum the quantity, but I would only want to count quantity for id "aaa" once and only count towards the latest status ("Delivered" in this case). I would make a visual that shows the quantities with status as its axis. I also need to add a date Slicer so I could go back in time. So, when I go before 11/5/2020, instead of "Delivered," it would switch back to "Shipped."

I tried several methods:

SUMMARIZE to a table filtering "MAX" date value if UID is the same. I found this doesn't work with the date slicer since it seems like it is not actually recalculating the filtering and just slicing away rows outside of the dates. Seems to be the same whether the SUMMARIZE is set as a new table or VAR in the Measure.
CALCULATE seems promising but I can't seem to figure out a syntax that filters that I need. Example of one that doesn't work (I also tried SUMX instead of SUM but that doesn't work either):

CALCULATE( SUM(Table1[Quantity]), FILTER(Table1, [StatusDate]=MAXX(FILTER(Table1,[UID]=EARLIER([UID])),[StatusDate]) )
I also tried adding a column that states whether if the row is "old" as well as a numerical "rank" to the different statuses. But once again, I run into the issue where the date slicer is not exactly filtering to recalculate those columns. For example, if the date slicer is set to 11/3/2020, it should add "3" to "Shipped" instead of "Delivered." But instead of that, it just removes the row which tells me that it is not actually recalculating the columns (like #1).

Any help would be appreciated :-) Thank you!

Joao Leal Joao Leal · Accepted Answer · 2020-11-30T14:22:54

You can try something like this:

Measure = 
VAR d = LASTDATE(Table1[StatusDate])
VAR tb = SUMMARIZE(FILTER(Table1, Table1[StatusDate] <= d), 
                  Table1[UID], 
                  "last", LASTDATE(Table1[StatusDate]))

RETURN CALCULATE(SUM(Table1[Quantity]), TREATAS(tb, Table1[UID], Table1[StatusDate]))

The tb variable contains a table which has the latest date per UID. You then use that to filter your main table with the TREATAS function.

One other alternative is to create a table with the RANK function ordered by date and then doing a SUM over that table, where Rank = 1.

Power BI calculate sum only last value of duplicate ID

1 Answers