Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
@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.
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
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |