Dynamic Period Value comparison of last N Months

[Anonymous]

I basically need to compare enagaged value to last compare N months as period and assign a value. which technically should fall like this: 

Date	Contact	Engaged	PreviousEngaged	Status
01-04-2020	CON-12	1
02-04-2020	CON-13	0
03-04-2020	CON-14	0
04-04-2020	CON-15	0
05-04-2020	CON-16	0
06-04-2020	CON-17	0
07-04-2020	CON-18	0
08-04-2020	CON-19	1
09-04-2020	CON-20	0
10-04-2020	CON-21	1
01-05-2020	CON-12	1	1	Retained
02-05-2020	CON-13	1	0	Not Retained
03-05-2020	CON-14	1	0	Not Retained
04-05-2020	CON-15	1	0	Not Retained
05-05-2020	CON-16	1	0	Not Retained
06-05-2020	CON-17	0	0	Retained
07-05-2020	CON-18	1	0	Not Retained
08-05-2020	CON-19	0	1	Not Retained
09-05-2020	CON-20	1	0	Not Retained
10-05-2020	CON-21	0	1	Not Retained
01-06-2020	CON-12	1	1	Retained
02-06-2020	CON-13	0	1	Not Retained
03-06-2020	CON-14	1	1	Retained
04-06-2020	CON-15	0	1	Not Retained
05-06-2020	CON-16	1	1	Retained
06-06-2020	CON-17	0	0	Retained
07-06-2020	CON-18	0	1	Not Retained
08-06-2020	CON-19	1	0	Not Retained
09-06-2020	CON-20	0	1	Not Retained
10-06-2020	CON-21	0	0	Retained

[amitchandak]

@Anonymous , not very clear. Try with a date calendar

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))

Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-12,month)),-12,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]

Thanks @Amit for the reply but that rolling 12 month period or rather N month period wanted to set up as dynamic value as in when we select the date field or text box so that we can mention the time period that should automatically  be implied in the calculation

[Greg_Deckler]

@Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
  __Current - __Previous

[Anonymous]

Thanks for the reply but my requirement is - I need to compare last N months window based on input selection which is dynamic and once that is calculated i have to compare the particular month value to present day-month and provide a value.