Forum Discussion
Need Help - Percent Change Calculation for Month to Month Trending
I need to create a Measure to show the percent of widget change from the prior month to the next. I'll be using this to create a visual to show month to month trending. Here is an example of what I'm looking for - the Net Change column is what I'm looking to create. Normally I'd be looking to use the Lag function, however it doesn't seem to be available in Power BI. Thanks in advance!
Month Widgets Net Change
Jan2019 100
Feb2019 200 100%
Mar2019 3000 1400%
Apr2019 2500 - 17%
May2019 1000 -60%
Jun2019 100 -90%
Jul2019 500 400%
Hi ScotchCat,
You can try to use the following measure formula to get the difference between current and previous amount:
measure = VAR currDate = MAX ( Table[Month] ) VAR prevDate = CALCULATE ( MAX ( Table[month] ), FILTER ( ALLSELECTED ( Table ), [Month] < currDate ) ) VAR prevAmount = CALCULATE ( SUM ( Table[Widgets] ), FILTER ( ALLSELECTED ( Table ), [Month] = prevDate ) ) RETURN DIVIDE ( SUM ( Table[Widgets] ) - prevAmount, prevAmount )Regards,
Xiaoxin Sheng
2 Replies
- amitchandak
Super User
ScotchCat , if you have date or you can create date from month year. You can use time intelligence with date table
Example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])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
Appreciate your Kudos. Hi ScotchCat,
You can try to use the following measure formula to get the difference between current and previous amount:
measure = VAR currDate = MAX ( Table[Month] ) VAR prevDate = CALCULATE ( MAX ( Table[month] ), FILTER ( ALLSELECTED ( Table ), [Month] < currDate ) ) VAR prevAmount = CALCULATE ( SUM ( Table[Widgets] ), FILTER ( ALLSELECTED ( Table ), [Month] = prevDate ) ) RETURN DIVIDE ( SUM ( Table[Widgets] ) - prevAmount, prevAmount )Regards,
Xiaoxin Sheng
