cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dannyboc
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      110/5/2020
00152368    AB46     7/27/2020       AR01    7/27/2020      17/27/2020
00152368    AB48     6/7/2020       AS01    6/8/2020      16/8/2020
00152368    AC02     2/9/2020       AS01    2/3/2020      12/9/2020
00152368   AB29     2/7/2020       AS01    2/3/2020      12/7/2020
00152368   AB01     2/3/2020       AS01    2/3/2020      12/3/2020
00152368   AC08     1/13/2020       AS01    10/8/2019      11/13/2020
00152368   AC10     1/10/2020       AS01    10/8/2019      11/10/2020
00152368   AC07     1/2/2020       AS01    10/8/2019      11/2/2020
00152368   AC15     10/9/2019       AS01    10/8/2019      110/9/2019
00152368   AC03     10/8/2019       AS01    10/8/2019      110/8/2019
00153000

   AB29

     6/1/2020       AS02    6/1/2020      16/1/2020

 

Please let me know if you have any ideas. Thanks

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

30.png

Best Regards,
Community Support Team _ Eason

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
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])))

 

 

 

Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

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

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

30.png

Best Regards,
Community Support Team _ Eason

 

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors