Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |