Forum Discussion

timothyh's avatar
timothyh
Regular Visitor
4 years ago

Adding month over month % difference column to table

Hi,

 

New to Power BI so I may be missing an easy solutions to this issue. I need to create a column in my table showing the month over month difference by percentage. I believe I need to add a new measure but can't figure out how to add that and the formula to show the difference. Below is the sample of data I'm trying to add the % difference for.

 

This is what I'm hoping it will look like:

Month/YearSessionsDifference
2022052095-39.03%
2022043436-25.50%
202203461217.06%
2022023940-0.73%
2022013969 
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    timothyh , the best is to have a separate date table. If data is only at the month level have monthyear table with rank on month year YYYYMM

     

     new column

    Month Rank = RANKX(all(Period),Period[year Month],,ASC,Dense)

     

    measures

    Measure
    This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))


    Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

     

    Take a diff

     

    If you have a date options are

     

    MTD = CALCULATE(AverageX(values('Date'[Date]), calculate(SUM(Table[Qunatity Produced])) ),DATESMTD('Date'[Date]))

    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]))
    next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
    this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

    previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    What you have shared in the image and table is the report that you want.  Please share the raw data.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi timothyh 

    You can try this, create the column below

    Difference = 
var _preMY= CALCULATE(MAX('Table'[Month/Year]),FILTER(ALL('Table'),'Table'[Month/Year]<EARLIER('Table'[Month/Year])))
var _cur= 'Table'[Sessions]
var _pre= CALCULATE(MAX('Table'[Sessions]),FILTER(ALL('Table'),'Table'[Month/Year]=_preMY))
return divide(_cur-_pre,_pre)

    result

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

    • timothyh's avatar
      timothyh
      Regular Visitor
      4 years ago

      Thank you for the reply. I'm probably missing something but when I try to create a new column in my data for Difference, then add the formula you suggested, the formula does not work. It doesn't recognize 'Table.' What I'm trying to do is show the difference month over month for each column in my report. Add a column next to Sessions showing the difference from 202204 to 202205, etc. Column would be the % difference from 202204 to 202205. Same with the Pageviews column.