cancel
Showing results for
Did you mean:
Frequent Visitor

## Sum of category quantities based on a selected date - slicer

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

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Super User

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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Frequent Visitor

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

Community Support

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

Announcements