March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
29 | |
20 | |
15 | |
14 | |
10 |
User | Count |
---|---|
62 | |
26 | |
25 | |
22 | |
15 |