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...
  • 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.

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