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.
Hi all,
What would be the easiest way to achieve the following goal?
My data is like this
I need to find out when some ID is New and when it is cancelled (exists in previous month but not anymore)
The end result I need is something like this:
Thanks in advance, appreciate your help! 🙂
Jenny
Solved! Go to Solution.
@dhannaa , where is month data. You can create a measure using time intelligence and date calendar
MTD = CALCULATE(countrows(Table),DATESMTD('Date'[Date]))
last MTD = CALCULATE(countrows(Table),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month = CALCULATE(countrows(Table),previousmonth('Date'[Date]))
last MTD (complete) = CALCULATE(countrows(Table),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(countrows(Table),previousmonth('Date'[Date]))
diff = [MTD]-[last MTD]
diff % = divide([MTD]-[last MTD],[last MTD])
you can create a color measure on diff and use that in conditional formatting using "Field option"
Color Date = if([diff] >0 ,"lightgreen","red")
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
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-Y...
Appreciate your Kudos.
@dhannaa , where is month data. You can create a measure using time intelligence and date calendar
MTD = CALCULATE(countrows(Table),DATESMTD('Date'[Date]))
last MTD = CALCULATE(countrows(Table),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month = CALCULATE(countrows(Table),previousmonth('Date'[Date]))
last MTD (complete) = CALCULATE(countrows(Table),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(countrows(Table),previousmonth('Date'[Date]))
diff = [MTD]-[last MTD]
diff % = divide([MTD]-[last MTD],[last MTD])
you can create a color measure on diff and use that in conditional formatting using "Field option"
Color Date = if([diff] >0 ,"lightgreen","red")
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
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-Y...
Appreciate your Kudos.
Thanks @amitchandak for super fast response.
Your solution sounds great and simple - don't know why I started this with Power Query in the first place :)!
Jenny
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |