Summation by category with Max calculated after Applicable of Slicers

[Anonymous]

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

[Zubair_Muhammad]

@Anonymous 

Try this MEASURE

Measure = CALCULATE(SUM(Table1[Sales]),LASTDATE(Table1[Sale Date]))

[Anonymous]

The last Date can vary by item during each year. And i'm not including item in the final display. 

[Ashish_Mathur]

Hi,

Zubair's formula works just fine.

[Anonymous]

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. 

[Anonymous]

Thank you so much it worked.

[Anonymous · Accepted 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