Showing latest date data as date slicer value changes

[Anonymous]

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

[v-eqin-msft · Accepted 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.

[Anonymous]

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

[amitchandak]

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

[Anonymous]

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.