I have a large table of many SKU's with 2 different statuses and dates. I calculated a max date column in Power Query, given the dates of the 2 statuses:
For 2 SKUs, the table looks as the table below.
I would like to sum all SKUs Qty based on a date selected from a slicer, eg. 6/10/2020, and according to their previous state max date:
So, if 6/10/2020 is selected in the slicer, the previous max date for SKU 00152368 is on 6/8/2020 and it sums 1, plus
1 qty for SKU 00153000 with max date on 6/1/2020. The expected result in the measure is 2.
Please let me know if you have any ideas. Thanks
Go to Solution.
Try formula as below:
Previous max date =
VAR currentdate =
SELECTEDVALUE ( 'Date'[Date] )
MAX ( 'Table'[Max Date] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[SKU] ), 'Table'[Max Date] < currentdate )
SUM ( 'Table'[Qty] ),
FILTER ( 'Table', 'Table'[Max Date] = [Previous max date] )
Best Regards,Community Support Team _ Eason
View solution in original post
@dannyboc , Use an independent date table in slicer
calculate(sumx(VALUES(DT1[SKU]), LASTNONBLANKVALUE(DT1[Status2 Time], sum(DT1[Qty]))), filter(Table, Table[Status2 Time] <= selectedvalue('Date'[Date])))
Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Hi @amitchandak, thank you for your reply.
I'm using an idependent date table (Date) on the slicer.
If my table is called "Table", I used the following measure:
= CALCULATE(SUMX(VALUES(Table[SKU]),LASTNONBLANKVALUE(Table[Max Date],SUM(Table[Qty]))),FILTER(Table,Table[Max Date]<=SELECTEDVALUE(Date[Date])))
The calculated value depends on the "Max Date" column, so "Status 1 Time" and "Status 2 Time" won't be used. However I get a wrong result, the expected result with the example is 2.
Can you please check? Thank you
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!