topic Re: Sum of Max Date for each each month in DAX Commands and Tips

Sum of Max Date for each each month

khaeshr46 — Sat, 21 Oct 2023 03:34:43 GMT

Hi,

I am looking to create a DAX formula to get the Total Sales of max date of each month for Scenario = "Actual", and Category = "Applicances".

Thanks!

Sample Data:

Date	Category	Scenario	Customer	Total Sales
4/26/2013	Bookcases	Plan	Adam Bellavance	6548.5
4/26/2013	Bookcases	Plan	Adam Bellavance	2153.55
4/26/2013	Appliances	Actual	Adam Bellavance	2472.66
11/7/2014	Tables	Plan	Adam Bellavance	1516.84
11/7/2014	Tables	Plan	Adam Bellavance	2036.86
11/7/2014	Tables	Actual	Adam Bellavance	1565.12
11/26/2014	Tables	Plan	Adam Bellavance	1237.56
11/26/2014	Appliances	Plan	Adam Bellavance	548.56
11/26/2014	Appliances	Actual	Adam Bellavance	367.67

Required Output

4/26/2013	Appliances	Actual	Adam Bellavance	2472.66
11/26/2014	Appliances	Actual	Adam Bellavance	367.67
				2840.33

Re: Sum of Max Date for each each month

ThxAlot — Sat, 21 Oct 2023 04:04:07 GMT

Re: Sum of Max Date for each each month

khaeshr46 — Sat, 21 Oct 2023 16:17:38 GMT

That's not quite what I am looking for.

Here's a measure breakdown for it but its not the right way at all. In this example its not Year focused. as taking in 2013 and 2014 dates in. But I would like for it to be year focused. Sum of Sales for each MONTH YEAR Scenario = "Actual", and Category = "Applicances".

Thanks alot! Really appriciate the help.

Measure =

var a =

CALCULATE(MAX(DATA[Date]),

DATA[Category] = "Appliances" && DATA[Scenario] = "Actual",

FILTER(DATA,DATA[Date] > DATE(2013,1,1) && DATA[Date] <= DATE(2013,12,31)))

var b =

CALCULATE(SUM(DATA[Total Sales]),

DATA[Category] = "Appliances" && DATA[Scenario] = "Actual",

FILTER(DATA,DATA[Date] = a))

var c =

CALCULATE(MAX(DATA[Date]),

DATA[Category] = "Appliances" && DATA[Scenario] = "Actual",

FILTER(DATA,DATA[Date] > DATE(2014,1,1) && DATA[Date] <= DATE(2014,12,31)))

var d =

CALCULATE(SUM(DATA[Total Sales]),

DATA[Category] = "Appliances" && DATA[Scenario] = "Actual",

FILTER(DATA,DATA[Date] = c))

Return b+d

Re: Sum of Max Date for each each month

ThxAlot — Sat, 21 Oct 2023 22:04:24 GMT

Sum of Max Date for Each Month = SUMX( VALUES( DATA[Customer] ), SUMX( VALUES( DATA[Yr-Mth] ), VAR __tr = TREATAS( { ( "Appliances", "Actual" ) }, DATA[Category], DATA[Scenario] ) VAR __max = CALCULATE( MAX( DATA[Date] ), KEEPFILTERS( __tr ), ALL( DATA[Date] ) ) RETURN CALCULATE( SUM( DATA[Total Sales] ), KEEPFILTERS( DATA[Date] = __max ), __tr ) ) )