Sum of Max Date for each each month

[khaeshr46]

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

[ThxAlot]

[khaeshr46]

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

[ThxAlot · Accepted solution]

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