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,
I would like to be able to calculate the percentage change of the car registrations during the previous 3 months compared to the current month's data.
I am very new to DAX, so I will just show you an example of how I used to calculate this when doing it in excel. My sample data is below as well.
Percentage change= (Current Month-Prior 3 Month Average)/ (Prior 3 Month Average)
EX: Percentage change for Brazil= (157-average(147,136,189))/average(147,136,189).
each month I will be adding the latest month's data to this list, so it would be great if this could auto- adjust to the previous 3 months vs the current month values.
Country | Date Added | Car Registrations |
Brazil | 2/17/2017 | 147 |
Brazil | 3/17/2017 | 136 |
Brazil | 4/17/2017 | 189 |
Brazil | 5/17/2017 | 157 |
Canada | 2/17/2017 | 128 |
Canada | 3/17/2017 | 113 |
Canada | 4/17/2017 | 125 |
Canada | 5/17/2017 | 191 |
China | 2/17/2017 | 2,218 |
China | 3/17/2017 | 1,633 |
China | 4/17/2017 | 2,096 |
China | 5/17/2017 | 1,722 |
Euro Area | 2/17/2017 | 895 |
Euro Area | 3/17/2017 | 900 |
Euro Area | 4/17/2017 | 908 |
Euro Area | 5/17/2017 | 904 |
India | 2/17/2017 | 244 |
India | 3/17/2017 | 240 |
India | 4/17/2017 | 263 |
India | 5/17/2017 | 254 |
Indonesia | 2/17/2017 | 100 |
Indonesia | 3/17/2017 | 87 |
Indonesia | 4/17/2017 | 95 |
Indonesia | 5/17/2017 | 101 |
Does anyone have any ideas for how to do this?
Solved! Go to Solution.
Hi let's go to find a solution:
Create these measures
CurrentMonth = VAR CMonth = MONTH ( LASTDATE ( Table1[Date Added] ) ) RETURN CALCULATE ( SUM ( Table1[Car Registrations] ), FILTER ( Table1, MONTH ( Table1[Date Added] ) = CMonth ) )
Average3MonthsPrev = CALCULATE ( AVERAGE ( Table1[Car Registrations] ), DATESBETWEEN ( Table1[Date Added], STARTOFMONTH ( DATEADD ( LASTDATE ( Table1[Date Added] ), -4, MONTH ) ), ENDOFMONTH ( DATEADD ( LASTDATE ( Table1[Date Added] ), -1, MONTH ) ) ) )
PercentageChange = DIVIDE ( [CurrentMonth] - [Average3MonthsPrev], [Average3MonthsPrev], 0 )
Let me know if works in your scenario.
Hi let's go to find a solution:
Create these measures
CurrentMonth = VAR CMonth = MONTH ( LASTDATE ( Table1[Date Added] ) ) RETURN CALCULATE ( SUM ( Table1[Car Registrations] ), FILTER ( Table1, MONTH ( Table1[Date Added] ) = CMonth ) )
Average3MonthsPrev = CALCULATE ( AVERAGE ( Table1[Car Registrations] ), DATESBETWEEN ( Table1[Date Added], STARTOFMONTH ( DATEADD ( LASTDATE ( Table1[Date Added] ), -4, MONTH ) ), ENDOFMONTH ( DATEADD ( LASTDATE ( Table1[Date Added] ), -1, MONTH ) ) ) )
PercentageChange = DIVIDE ( [CurrentMonth] - [Average3MonthsPrev], [Average3MonthsPrev], 0 )
Let me know if works in your scenario.
Is there a way to update the Current Month and Average measure so that it picks up the last date for each country? For example, sometimes a few countries have not made an update to their data in a few months so we may be in June for other months but their data only goes up to February. In that case the formula does not work.
Here's an example:
Country | Date Added | Car Registrations |
Thailand | 1/17/2017 | 32 |
Thailand | 2/17/2017 | 35 |
Thailand | 3/17/2017 | 41 |
Thailand | 4/17/2017 | 27 |
United States | 11/17/2016 | 488 |
United States | 12/17/2016 | 554 |
United States | 1/17/2017 | 441 |
United States | 2/17/2017 | 502 |
For the US... here is what I get using the measures in the previous message:
But it should be 502 current month and 494.33 for the average so a 1.5% change. (502-494.33)/494.33
This worked Perfectly!!! Thank you so much.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |