Need Help - Percent Change Calculation for Month to Month Trending

[ScotchCat]

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%

[v-shex-msft · Accepted solution]

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

[amitchandak]

@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.