Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Month on Month - % Variance

 

Hello Guys

 

I need your help

 

I would like to create a chart in Power BI which shows the % of Variance  Month over Month, but, I am not succeed.

in the table below I have the number of tickets closed every month. The column "Closed" is a measure.

Is there a way to calculate this?

 

 

 

 

MonthYearClosed%
Jan-172811-
Feb-172591-8%
Mar-17298615%
Apr-172775-7%
May-172737-1%
Jun-1728143%
Jul-17318913%
Aug-17395724%
Sep-173130-21%
Show and Tell
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    Anonymous

     

    The solution that Ashish_Mathur provided requires a full calendar date column in your data model. If you only have that "Month" column in your table, you should add a numeric "YearMonthNumber" column for this calculation. Please refer to sample below:

     

    YearMonthNumber = VALUE(LEFT(FORMAT(Table3[MonthYear],"yyyyMMdd"),6))
    variance% =
IFERROR (
    SUM ( Table3[Closed] )
        / CALCULATE (
            SUM ( Table3[Closed] ),
            FILTER (
                ALL ( Table3 ),
                Table3[YearMonthNumber]
                    = MAX ( Table3[YearMonthNumber] ) - 1
            )
        )
        - 1,
    BLANK ()
)

     

    Regards,

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this

     

    =IFERROR([Closed]/CALCULATE([Closed],PREVIOUSMONTH('CALENDAR'[Date]))-1,BLANK())

     

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      hi Ashish_Mathur

      For some reason the number are not being displayed in the chart

      THe measure is correct

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    Anonymous

     

    The solution that Ashish_Mathur provided requires a full calendar date column in your data model. If you only have that "Month" column in your table, you should add a numeric "YearMonthNumber" column for this calculation. Please refer to sample below:

     

    YearMonthNumber = VALUE(LEFT(FORMAT(Table3[MonthYear],"yyyyMMdd"),6))
    variance% =
IFERROR (
    SUM ( Table3[Closed] )
        / CALCULATE (
            SUM ( Table3[Closed] ),
            FILTER (
                ALL ( Table3 ),
                Table3[YearMonthNumber]
                    = MAX ( Table3[YearMonthNumber] ) - 1
            )
        )
        - 1,
    BLANK ()
)

     

    Regards,

  • PBIdashboards's avatar
    PBIdashboards
    Post Patron
    2 months ago

    The YearMonthNumber workaround in the accepted answer works when you don't have a Date table, but it breaks if your data spans multiple years  202312 and 202412 will be treated as consecutive months in the calculation.

    Cleaner fix: add a proper Date table (even a simple one via Power Query) and use DATEADD:

     
     
    Prev Month = CALCULATE([Closed], DATEADD('Date'[Date], -1, MONTH))
    MoM % Variance = DIVIDE([Closed] - [Prev Month], ABS([Prev Month]))

    For teams reporting ticket volumes month-over-month regularly, Flexa Tables on AppSource adds MoM variance as a one-click button in the published report  no DAX, no Date table dependency