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.

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.