Forum Discussion

Emanuel's avatar
Emanuel
Helper I
5 years ago
Solved

Increase/Decrease percentage

Hello,

 

I need some help calculating the increase/decrease in percentage of the values compared to the previous month:

 

Month        OnGoing

01-Jun-20499
01-Jul-20397
01-Aug-20467
01-Sep-20494

 

It should show something like this:

MonthOnGoingincrease/decrease
2020-06499 
2020-07397-20%
2020-0846718%
2020-094946%

 

Thanks for the help

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    5 years ago

    Hi Emanuel ,

     

    Measure = 
var lastmonth = CALCULATE(SUM('Table'[OnGoing]),DATEADD('Table'[Month],-1,MONTH))
var currmonth = CALCULATE(SUM('Table'[OnGoing]))
return
DIVIDE(currmonth-lastmonth,lastmonth)

     

     

2 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    5 years ago

    Hi Emanuel ,

     

    Measure = 
var lastmonth = CALCULATE(SUM('Table'[OnGoing]),DATEADD('Table'[Month],-1,MONTH))
var currmonth = CALCULATE(SUM('Table'[OnGoing]))
return
DIVIDE(currmonth-lastmonth,lastmonth)

     

     

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Emanuel , 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 :radacad sqlbi My Video Series Appreciate your Kudos.

     

     

    Please provide your feedback comments and advice for new videos
    Tutorial Series Dax Vs SQL Direct Query PBI Tips
    Appreciate your Kudos.