Forum Discussion

pfarahani's avatar
pfarahani
Helper II
5 years ago
Solved

Calculate Percentage Change from Previous Row

Hello,   I am trying to create a DAX measure to get the Percentage Change from Month to Month. Problem is I do not have a traditional Dates table, my Dates table is more like a String table with Da...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    pfarahani , Try like this example , Date here us your Month year table

     

     

    This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[index]=max('Date'[index])))
    Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[index]=max('Date'[index])-1))

     

    diff = [This Month]-[Last Month]
    diff % = divide([This Month]-[Last Month],[Last Month])

amitchandak's avatar
amitchandak
Super User
5 years ago

pfarahani , Try like this example , Date here us your Month year table

 

 

This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[index]=max('Date'[index])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[index]=max('Date'[index])-1))

 

diff = [This Month]-[Last Month]
diff % = divide([This Month]-[Last Month],[Last Month])

pfarahani's avatar
pfarahani
Helper II
5 years ago

amitchandak , worked out pretty nice i think, 1 small issue remains. Below is the chart, not bad, however my data ends with December 2019, but its still trying to calculate for the January 2020. Is their a solution using ISBLANK or an IF to stop after Dec 2019. thanks 

 

 

  • pfarahani's avatar
    pfarahani
    Helper II
    5 years ago

    amitchandak I think I got something here, 

    I took the code you provided for diff% and modify to this:

    diff % = IF( ISBLANK('_Measures Table'[This Month])
    , BLANK(), DIVIDE([This Month]-[Last Month],[Last Month]))
     
    Seems to work nice. One more question, the part i circled at the top, what is it calculating there? thanks