Benchmarking month as variable

Hi SpaneshS 

(1)Create a Calendar Date table as a slicer to filter data .

(2)Create measures to return the sum of the sales from previous month ,current month and last year same month .

Current month sales = CALCULATE(SUM('Table 1'[Value]),FILTER('Table 1',MONTH('Table 1'[Date])=MONTH(MAX('Date 1'[Date]) )))

Previous month sales = CALCULATE(SUM('Table 1'[Value]),FILTER('Table 1',MONTH('Table 1'[Date])=MONTH(MAX('Date 1'[Date]))-1))

current year sales = CALCULATE(SUM('Table 2'[Value]),FILTER('Table 2',YEAR('Table 2'[Date])=YEAR(MAX('Date 2'[Date])) && MONTH('Table 2'[Date])=MONTH(MAX('Date 2'[Date]))))

last year sales = CALCULATE(SUM('Table 2'[Value]),FILTER('Table 2',YEAR('Table 2'[Date])=YEAR(MAX('Date 2'[Date]))-1 && MONTH('Table 2'[Date])=MONTH(MAX('Date 2'[Date]))))

(3) Subtract the two measures separately to get the difference value

diff = [Current month sales]-[Previous month sales]

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.