Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi I have a table as attached. Below screenshot shows part of the data. Now I want to use a table to show the qty of each product for each area . Each area has a few shops (denoted as shop_id), each shop has multiple update_time, we need to calculate the qty only on latest update_time for each shop and sum the value if they are in the same area. And there is a date slicer, the final display should change as the seletected data range changes
For instance, below screen shot shows the expected result if slicer date ranges from Jan.01,2021 to Jul,31,2021
I wanted to create calculated table to get data with only latest update_time, however, calculated table cannot change as slicer value changes. Any idea, any DAX can solve this issue. Below is the data.
product | shop_id | update_time | qty | Area |
cat1 | S1 | 5/5/2021 5:55 | 30 | c1 |
cat1 | S1 | 5/5/2021 5:55 | 12 | c1 |
cat1 | S1 | 5/5/2021 5:55 | 22 | c1 |
cat1 | S1 | 5/5/2021 5:55 | 23 | c1 |
cat1 | S2 | 7/8/2021 4:00 | 12 | c1 |
cat2 | S2 | 7/8/2021 4:00 | 14 | c1 |
cat2 | S2 | 7/8/2021 4:00 | 16 | c1 |
cat2 | S2 | 7/8/2021 4:00 | 34 | c1 |
cat3 | S1 | 7/8/2021 4:30 | 20 | c1 |
cat3 | S1 | 7/8/2021 4:30 | 12 | c1 |
cat3 | S3 | 1/13/2021 3:00 | 15 | c2 |
cat3 | S3 | 1/13/2021 3:00 | 17 | c2 |
cat4 | S3 | 1/13/2021 3:00 | 30 | c2 |
cat1 | S3 | 1/13/2021 3:00 | 12 | c2 |
cat1 | S3 | 1/13/2021 3:00 | 10 | c2 |
cat2 | S3 | 1/13/2021 3:00 | 9 | c2 |
cat2 | S3 | 1/13/2021 3:00 | 13 | c2 |
cat3 | S3 | 6/12/2021 2:00 | 11 | c2 |
cat3 | S3 | 6/12/2021 2:00 | 22 | c2 |
cat4 | S3 | 6/12/2021 2:00 | 15 | c2 |
cat1 | S3 | 6/12/2021 2:00 | 4 | c2 |
cat3 | S5 | 8/12/2021 1:00 | 12 | c3 |
cat4 | S5 | 8/12/2021 1:00 | 11 | c3 |
cat1 | S5 | 8/12/2021 1:00 | 9 | c3 |
cat1 | S5 | 8/12/2021 1:00 | 4 | c3 |
cat2 | S5 | 8/12/2021 1:00 | 12 | c3 |
cat1 | S4 | 8/2/2021 1:00 | 3 | c2 |
cat1 | S4 | 8/2/2021 1:00 | 45 | c2 |
cat1 | S4 | 8/2/2021 1:00 | 23 | c2 |
cat2 | S4 | 8/2/2021 1:00 | 12 | c2 |
cat2 | S4 | 8/2/2021 1:00 | 4 | c2 |
cat2 | S4 | 8/2/2021 1:00 | 23 | c2 |
cat2 | S4 | 8/2/2021 1:00 | 23 | c2 |
cat2 | S4 | 8/2/2021 1:00 | 2 | c2 |
cat2 | S4 | 8/2/2021 1:00 | 1 | c2 |
cat3 | S4 | 8/2/2021 1:00 | 43 | c2 |
cat1 | S4 | 4/2/2021 1:00 | 23 | c2 |
cat2 | S4 | 4/2/2021 1:00 | 21 | c2 |
cat2 | S4 | 4/2/2021 1:00 | 12 | c2 |
cat2 | S4 | 4/2/2021 1:00 | 16 | c2 |
cat2 | S4 | 4/2/2021 1:00 | 1 | c2 |
cat2 | S4 | 4/2/2021 1:00 | 7 | c2 |
cat2 | S4 | 4/2/2021 1:00 | 34 | c2 |
cat3 | S4 | 4/2/2021 1:00 | 32 | c2 |
cat1 | S6 | 3/3/2021 2:34 | 12 | c3 |
cat1 | S6 | 3/3/2021 2:34 | 31 | c3 |
cat1 | S6 | 3/31/2021 2:34 | 12 | c3 |
cat1 | S6 | 3/31/2021 2:34 | 31 | c3 |
Solved! Go to Solution.
Hi @Anonymous ,
Please try:
1. Get the lastest date of each area, product and shop:
Flag =
var _last=MAXX(FILTER(ALLSELECTED('Table'),[Area]=MAX('Table'[Area]) && [product]=MAX('Table'[product]) && [shop_id]=MAX('Table'[shop_id]) ),[update_time])
return IF(MAX('Table'[update_time])=_last,1,0)
2. Add a new table:
New Table = CROSSJOIN(VALUES('Table'[Area]),VALUES('Table'[product]))
3.
Measure = CALCULATE(SUM('Table'[qty]),FILTER('Table',[Area]=MAX('New Table'[Area]) && [product]=MAX('New Table'[product]) &&[Flag]=1))+0
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please try:
1. Get the lastest date of each area, product and shop:
Flag =
var _last=MAXX(FILTER(ALLSELECTED('Table'),[Area]=MAX('Table'[Area]) && [product]=MAX('Table'[product]) && [shop_id]=MAX('Table'[shop_id]) ),[update_time])
return IF(MAX('Table'[update_time])=_last,1,0)
2. Add a new table:
New Table = CROSSJOIN(VALUES('Table'[Area]),VALUES('Table'[product]))
3.
Measure = CALCULATE(SUM('Table'[qty]),FILTER('Table',[Area]=MAX('New Table'[Area]) && [product]=MAX('New Table'[product]) &&[Flag]=1))+0
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, thanks for the solution. I follow your method, but it all retun zero, not sure why. But if I skip step 2 and change the DAX in step 3 to below. It can return proper result, for those equal to zero, the area and product does not appear in final table though. You know why is that so?
Measure = CALCULATE(SUM('Table'[qty]),FILTER('Table',[Flag]=1))+0
One additional problem, is it possible to create a calculated column for flag in Table to indicate whether the record is latest or not, it must be dynamic. So to get latest result, I just need to filter Table with condition Flag = 1
@Anonymous , Try like
calculate(lastnonblankvalue(updated_time, Sum(Table[Qty])), allexcept(Table, Table[Area], Table[product]))
or
calculate(lastnonblankvalue(updated_time, Sum(Table[Qty])), filter(allselected(Table), Table[Area] = max(Table[Area]) && Table[product] =max(Table[product]) ))
Hi @amitchandak , many thanks, however, this measure can only get the qty of the latest update_time record in each area, I need to sum up, within the date range, the qty of the latest update_time record of each shop in the same area. For instance, as shown below, when Area = C1, product = cat1, the expected qty should be 99, using your suggestted measure return 87. Please advise, the final display does not allow to show the shop column.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
12 | |
11 | |
10 |