3 full months trailing graph

[SammyNed]

Hi Everyone

Can someone please assist?

My stakeholders are excel lovers and want a view as below (dummy excel data).

If they select a month in the slicer, then the graph must show

• the trailing 3 months of sales and previous year sales;
• and then the forecast and target for that selected slicer month.

My attempts:

So i created a seperate date table, that does not have a relationship to the other tables and then referenced it in the measure below to get the sales for the selected slicer date (and 3 months trailing).

3MonthSales =

    VAR CurrentDate = MAX('Previous Date'[EndofMonth])

    VAR SelectedDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-3,DAY(CurrentDate))

    VAR Result =

    CALCULATE(sum[sales],

    Filter( Table,table[date]>= SelectedDate && Table[date]<= CurrentDate))

RETURN

Result

Problem 1:

However practically i get more or sometimes less than 3 exact months, as seen in the powerbi graph below. I believe this is because of the number of days in a month, but i want the full 3 months (selected month and 2 prior; if the selected month is not complete yet, then still only show me the incomplete month and 2 full months prior.)

Problem 2:

How can I get the previous year sales, this calculation does not work for previous year?

Thanking you in advance

😁

[v-kaiyue-msft · Accepted solution]

Hi @SammyNed ,

Thanks for the reply from @SachinNandanwar , please allow me to provide another insight: 

I use these sample data to demonstrate.

1. create a calculation table. And adjust the date format.

Table =
DISTINCT(‘financials’[Date])

2. create MEASURE.

MEASURE =
VAR _cur_mon =
EOMONTH ( SELECTEDVALUE ( ‘Table’[Date] ), 0 )
VAR _3_mon =
EOMONTH ( SELECTEDVALUE ( ‘Table’[Date] ), -3 ) + 1
RETURN
IF (
MAX ( ‘financials’[Date] ) <= _cur_mon
&& MAX ( ‘financials’[Date] ) >= _3_mon,
1
)

3. Filter the data in the bar chart where MEASURE is 1.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[SammyNed]

thank you @v-kaiyue-msft . This really helped. I think my issue was with the date table (it was in days and not months)

[SachinNandanwar]

Please go through this article

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/