CALCULATE LAST 3 MONTHS VERSUS ITS PREVIOUS PERIOD - DYNAMIC

[Anonymous]

Hi experts!

Could anyone help me the formula for this?

I am trying to get the sum of sales from the last 3 months and compare it to the previous 3 months
(so it would be like SUM of April, May and June vs. Jan, Feb and March)
The thing is I want it to have it set up in a dynamic way, so it always gets the latest months when I input new monthy data over time!

Thanks for the help! 😉

[Ashish_Mathur]

Hi,

Assuming your Sales table is updated only once every month and you have a Calendar table which expand as the Sales table gets additional rows each month, try these measures

Total sales = SUM(Data[Sales])

Sales for 3 months ended the current month = calculate([Total sales],datesbetween(calendar[date],edate(eomonth(max(calendar[date]),-1)+1,-2),max(calendar[date])))

Sales for previous 3 months = calculate([Total sales],datesbetween(calendar[date],edate(eomonth(max(calendar[date]),-1)+0,-5),edate(eomonth(max(calendar[date]),-3)))

In the slicer, select any 1 month and 1 year (both from the Calendar table).

If this does not help, then share the link from where i can download your PBI file.

[amitchandak · Accepted solution]

@Anonymous , Try with a date calendar.

example

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-3,MONTH))
Rolling 3 till last 3 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-3,month)),-3,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

@amitchandak Perfect! It worked! Thanks for the help 😉