Sum of category quantities based on a selected date - slicer

[dannyboc]

Hello,

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.

SKU	Status1	Status1 Time	Status2	Status2 Time	Qty	Max Date
00152368	AB46	7/27/2020	AD04	10/5/2020	1	10/5/2020
00152368	AB46	7/27/2020	AR01	7/27/2020	1	7/27/2020
00152368	AB48	6/7/2020	AS01	6/8/2020	1	6/8/2020
00152368	AC02	2/9/2020	AS01	2/3/2020	1	2/9/2020
00152368	AB29	2/7/2020	AS01	2/3/2020	1	2/7/2020
00152368	AB01	2/3/2020	AS01	2/3/2020	1	2/3/2020
00152368	AC08	1/13/2020	AS01	10/8/2019	1	1/13/2020
00152368	AC10	1/10/2020	AS01	10/8/2019	1	1/10/2020
00152368	AC07	1/2/2020	AS01	10/8/2019	1	1/2/2020
00152368	AC15	10/9/2019	AS01	10/8/2019	1	10/9/2019
00152368	AC03	10/8/2019	AS01	10/8/2019	1	10/8/2019
00153000	AB29	6/1/2020	AS02	6/1/2020	1	6/1/2020

Please let me know if you have any ideas. Thanks

[amitchandak]

@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

[dannyboc]

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

[v-easonf-msft · Accepted solution]

Hi,  @dannyboc 

Try formula as below:

Previous max date = 
VAR currentdate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        MAX ( 'Table'[Max Date] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[SKU] ), 'Table'[Max Date] < currentdate )
    )

Count = 
CALCULATE (
    SUM ( 'Table'[Qty] ),
    FILTER ( 'Table', 'Table'[Max Date] = [Previous max date] )

Best Regards,
Community Support Team _ Eason