topic Re: 3 full months trailing graph in DAX Commands and Tips

3 full months trailing graph

SammyNed — Tue, 30 Jul 2024 10:43:10 GMT

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

😁

Re: 3 full months trailing graph

SachinNandanwar — Tue, 30 Jul 2024 12:37:18 GMT

Please go through this article

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

Re: 3 full months trailing graph

Anonymous — Wed, 31 Jul 2024 05:48:25 GMT

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.

Re: 3 full months trailing graph

SammyNed — Mon, 05 Aug 2024 12:06:15 GMT

thank you Anonymous . This really helped. I think my issue was with the date table (it was in days and not months)