This questions may have been answered but after scouring for hours over the forum i could not find a solutuion that works . DAX Gurus please help.
I need to get the Latest Sale data grouped by category where the year column is a slicer
Here is the data set
If user selects 2018 in the year slicer . The resulting rows should be the sum of Sales for latest record for each item from 2018 grouped by the Category. Result would be
(i.e sum of A1 Feb 2018 sales + A2 Feb 2018 Sales grouped by category A and Similarly for B)
if user selected 2019
Result would be sum of A1 Feb 2019 sales (22) + A2 Feb 2019 Sales(42) grouped by category A (and similarly for B)
If user selects 2018 and 2019, only the latest records (2019) should be picked up . Result would be
(i.e sum of A1 Feb 2019 sales + A2 Feb 2019 Sales grouped by category A and Similarly for B)
Go to Solution.
@BI_Pro - Try this Measure:
Last Sales =
SUMMARIZE(mytable, mytable[Category],mytable[Item],"Sale Date",MAX(mytable[Sale Date])),
The filter limits to only those columns which match the Category, Item, and Max Sales Date.
TREATAS is necessary to avoid the other columns creeping back in. Refer to this site to better understand it.
View solution in original post
Try this MEASURE
Measure = CALCULATE(SUM(Table1[Sales]),LASTDATE(Table1[Sale Date]))
The last Date can vary by item during each year. And i'm not including item in the final display.
Zubair's formula works just fine.
It didnt work for me. Let me elaborate. There can be an item A3 with only record in Jan 2019 . When user selects jan 2018 It should pick up
A1- from Feb 2019
A2 from Feb 2019
A3 from Jan 2019 and sum up the data by category.
Thank you so much it worked.
Do you need help in Spanish? Check out our new Spanish community section.
Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.
Watch Microsoft Business Applications Summit sessions on-demand.