Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Showing latest date data as date slicer value changes

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.

productshop_idupdate_timeqtyArea
cat1S15/5/2021 5:5530c1
cat1S15/5/2021 5:5512c1
cat1S15/5/2021 5:5522c1
cat1S15/5/2021 5:5523c1
cat1S27/8/2021 4:0012c1
cat2S27/8/2021 4:0014c1
cat2S27/8/2021 4:0016c1
cat2S27/8/2021 4:0034c1
cat3S17/8/2021 4:3020c1
cat3S17/8/2021 4:3012c1
cat3S31/13/2021 3:0015c2
cat3S31/13/2021 3:0017c2
cat4S31/13/2021 3:0030c2
cat1S31/13/2021 3:0012c2
cat1S31/13/2021 3:0010c2
cat2S31/13/2021 3:009c2
cat2S31/13/2021 3:0013c2
cat3S36/12/2021 2:0011c2
cat3S36/12/2021 2:0022c2
cat4S36/12/2021 2:0015c2
cat1S36/12/2021 2:004c2
cat3S58/12/2021 1:0012c3
cat4S58/12/2021 1:0011c3
cat1S58/12/2021 1:009c3
cat1S58/12/2021 1:004c3
cat2S58/12/2021 1:0012c3
cat1S48/2/2021 1:003c2
cat1S48/2/2021 1:0045c2
cat1S48/2/2021 1:0023c2
cat2S48/2/2021 1:0012c2
cat2S48/2/2021 1:004c2
cat2S48/2/2021 1:0023c2
cat2S48/2/2021 1:0023c2
cat2S48/2/2021 1:002c2
cat2S48/2/2021 1:001c2
cat3S48/2/2021 1:0043c2
cat1S44/2/2021 1:0023c2
cat2S44/2/2021 1:0021c2
cat2S44/2/2021 1:0012c2
cat2S44/2/2021 1:0016c2
cat2S44/2/2021 1:001c2
cat2S44/2/2021 1:007c2
cat2S44/2/2021 1:0034c2
cat3S44/2/2021 1:0032c2
cat1S63/3/2021 2:3412c3
cat1S63/3/2021 2:3431c3
cat1S63/31/2021 2:3412c3
cat1S63/31/2021 2:3431c3
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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