Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Category | Year | Item | Sale Date | Sales |
A | 2018 | A1 | Jan-18 | 10 |
A | 2018 | A1 | Feb-18 | 20 |
A | 2018 | A2 | Jan-18 | 30 |
A | 2018 | A2 | Feb-18 | 40 |
A | 2019 | A1 | Jan-19 | 12 |
A | 2019 | A1 | Feb-19 | 22 |
A | 2019 | A2 | Jan-19 | 32 |
A | 2019 | A2 | Feb-19 | 42 |
B | 2018 | B1 | Jan-18 | 100 |
B | 2018 | B1 | Feb-18 | 200 |
B | 2018 | B2 | Jan-18 | 300 |
B | 2018 | B2 | Feb-18 | 400 |
B | 2019 | B1 | Jan-19 | 110 |
B | 2019 | B1 | Feb-19 | 120 |
B | 2019 | B2 | Jan-19 | 130 |
B | 2019 | B2 | Feb-19 | 140 |
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)
Category | Sales |
A | 60 |
B | 600 |
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)
Category | Sales |
A | 64 |
B | 260 |
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)
Category | Sales |
A | 64 |
B | 260 |
Solved! Go to Solution.
@Anonymous - Try this Measure:
Last Sales = CALCULATE( SUM(mytable[Sales]), TREATAS ( SUMMARIZE(mytable, mytable[Category],mytable[Item],"Sale Date",MAX(mytable[Sale Date])), mytable[Category], mytable[Item], 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.
Cheers!
Nathan
@Anonymous
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.
Hi,
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.
@Anonymous - Try this Measure:
Last Sales = CALCULATE( SUM(mytable[Sales]), TREATAS ( SUMMARIZE(mytable, mytable[Category],mytable[Item],"Sale Date",MAX(mytable[Sale Date])), mytable[Category], mytable[Item], 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.
Cheers!
Nathan
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |