Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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

yalesg_0-1637332085945.png

For instance, below screen shot shows the expected result if slicer date ranges from Jan.01,2021 to Jul,31,2021

yalesg_1-1637332547195.png

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
1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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:

Eyelyn9_2-1637737653535.png

 

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.

View solution in original post

4 REPLIES 4
v-eqin-msft
Community Support
Community Support

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:

Eyelyn9_2-1637737653535.png

 

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
Not applicable

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
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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.

yalesg_0-1637382910300.png

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.