Forum Discussion
Measure for percentage change using date fields
Hi,
Super new to Power BI but I have a table for capturing when an objective was created with a date/time column called 'createdOn'
I want to calculate how many have been created per month so I have a measure for 'Total Objectives' as:
COUNTA('reviews Objective'[CreatedOn])
Which when put on a table I can get the following:
What I would now like to do is to add another column with the percentage change from the previous month.
I have looked and googled and all queries seem to be 'Sums' rather than 'Counts' and I'm wondering if what I am trying to do is possible.
TIA
Hi Chase234 ,
Please try like:
the percentage change = VAR _pre = CALCULATE([Total Objectives],PREVIOUSMONTH('Calendar'[Date])) VAR _change = DIVIDE([Total Objectives]-_pre,_pre) RETURN _changeBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
4 Replies
Chase234 , you should always use date table and time intelligence
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9Sorry I have been out for a few weeks but your links have been really helpful mixed with the DAX from the above solution as within your first video I was realised I was missing a Calendar/Date table to join up to my other tables.
Hi Chase234 ,
Please try like:
the percentage change = VAR _pre = CALCULATE([Total Objectives],PREVIOUSMONTH('Calendar'[Date])) VAR _change = DIVIDE([Total Objectives]-_pre,_pre) RETURN _changeBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Sorry, I have been out of action for a few weeks but thank you this is exactly the equation I needed to get what get my desired results!
