Sum of values by date for each category

[abhay03]

In my report, there is a report-level filter on the current month. I have to show the values of Amount for each category side by side for 2 different time period: first for the current month and for 2 months prior. Below are the two tables:

DimCategory

Key	Category
1	ABC
2	DEF
3	GHI

FactCategory

Key	Amount	Date
1	10	09/01/2017
2	20	09/01/2017
2	30	09/11/2017
3	40	09/01/2017
1	50	10/01/2017
2	60	10/02/2017
3	70	10/11/2017
1	80	11/15/2017
2	90	11/16/2017
3	80	11/17/2017
3	70	11/18/2017

I tried writing measure to calculate two months prior Amount for each category but the result I am getting is the sum of all category for that time period. 

Measure = CALCULATE(SUM(FactCategory[Amount]), 
           FILTER(ALL(FactCategory), MONTH(FactCategory[Date]) = 9))


Measure 2 = SUMX(
	SUMMARIZE(
	FactCategory,
	DimCategory[Category],
	"VALUE",
	CALCULATE(SUM(FactCategory[Amount]), FILTER(ALL(FactCategory), MONTH(FactCategory[Date]) = 9))
	),
	[VALUE]
)

 

How can I get the below output:

Category	Amount	Amount 2 months prior
ABC	80	10
DEF	90	50
GHI	150	40

[v-shex-msft · Accepted solution]

Hi @abhay03,

You can try to use below formula to get previous data.

Measure:

Previous 2 Month =
VAR current_Date =
    MAX ( FactCategory[Date] )
RETURN
    SUMX (
        FILTER (
            ALL ( FactCategory ),
            [Key] = MAX ( DimCategory[Key] )
                && FORMAT ( [Date], "yyyy mmm" )
                    = FORMAT (
                        DATE ( YEAR ( current_Date ), MONTH ( current_Date ) - 2, 1 ),
                        "yyyy mmm"
                    )
        ),
        [Amount]
    )

Regards,

Xiaoxin Sheng

[abhay03]

Thanks, @v-shex-msft. This is exactly what I was looking for.