topic Showing latest date data as date slicer value changes in DAX Commands and Tips

Showing latest date data as date slicer value changes

Anonymous — Fri, 19 Nov 2021 14:44:29 GMT

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

Re: Showing latest date data as date slicer value changes

amitchandak — Fri, 19 Nov 2021 15:40:01 GMT

Anonymous , Try like

calculate(lastnonblankvalue(updated_time, Sum(Table[Qty])), allexcept(Table, Table[Area], Table[product]))

calculate(lastnonblankvalue(updated_time, Sum(Table[Qty])), filter(allselected(Table), Table[Area] = max(Table[Area]) && Table[product] =max(Table[product]) ))

Re: Showing latest date data as date slicer value changes

Anonymous — Sat, 20 Nov 2021 04:38:12 GMT

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.

Re: Showing latest date data as date slicer value changes

Anonymous — Wed, 24 Nov 2021 07:07:49 GMT

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

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.

Re: Showing latest date data as date slicer value changes

Anonymous — Sun, 28 Nov 2021 07:23:37 GMT

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