Benchmarking month as variable

[SpaneshS]

Hi all,

I've requirement to compare Current month sales with Last month sales ,Last year same month sales and Benchmarking month sales.

But Benchmarking month need to keep it as variable.we need to change the Benchmarking month whenever we required and compare with current month sales. 

Here Current month sales is whatever the date we selected and same thing Benchmarking month also need to show in same way.

If I do same Both data interacting.

How can I implement this ???

Kudos in advance!!

[Anonymous]

Hi @SpaneshS 

So what are your dynamic rules? If there are no rules, you can only set the slicer separately to filter the data of different tables and then compare them.

Best Regards

Community Support Team _ Ailsa Tao

[SpaneshS]

Hi @Anonymous 

My dynamic rules are the values should be cumulative,BM should be either selective or we can make it as Top sales in that particular Financial year (march - april)

[Anonymous]

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.

[SpaneshS]

Hi thanks for reply

This Comparison works for fixed month for suppose Last month and last year,But I required variable kind of thing I need to select different month If it is july i need to compare with january or Feb