Forum Discussion

M_SBS_6's avatar
M_SBS_6
Icon for Helper V rankHelper V
2 years ago
Solved

Month on month variance %

Hi, I'm wanting to add in a month on month variance % difference onto a table but not quote sure how.

 

Here's my table 

 

App_Month app_Val

1. 100

2. 50

3. 200

4. 100

5. 50

6. 50

7. 100

8. 200

9. 100

10. 100

11. 50

12. 100

 

What I want to see is Jan (1) to show 0% but in month 2, the percentage difference (measure called app_variance) from Jan to Feb -50% and month 3, the difference from March to Feb 300% etc.

 

I do have a date column app_date.

 

  • Any idea please? 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi M_SBS_6 

    I understand your problem:

    Since you have more than one year in your table, filtering by month will add the values from the different years together.

    Here is my solution:

    I used the date column instead of the month column, and I also added some dates from different years for testing.

    Then I created a calculated column:

    app_Variance = 

var A = IF(MONTH('Table'[App_Date])<>1,
//It is used to determine when the date falls in January of each year
            CALCULATE(
                SUM('Table'[App_Val]),
                FILTER(
                    'Table',
                    MONTH('Table'[App_Date]) = MONTH(EARLIER('Table'[App_Date])) -1
                    &&
                    YEAR('Table'[App_Date]) = YEAR(EARLIER('Table'[App_Date]))
                )
//When the month is not in January, the Val value of the previous month is extracted,
            ),
            CALCULATE(
                SUM('Table'[App_val]),
                FILTER(
                    'Table',
                    MONTH('Table'[App_Date]) = 12
                    &&
                    YEAR('Table'[App_Date]) = YEAR(EARLIER('Table'[App_Date]))-1
                )
        )
//When it is in January, the Val value of December of the previous year is extracted
)
var B = ('Table'[App_Val]-A)/A
//compute
RETURN IF(A<>0,B)

    The result is as follow:

    Best Regards,

    Zhengdong Xu

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

8 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 years ago

    Something like the following might work for you.

    app_variance = 
var _prevMonthValue =
LOOKUPVALUE('Table'[app_val], 'Table'[App_Month], SELECTEDVALUE('Table'[App_Month]) - 1)
var _currentValue = 
SELECTEDVALUE('Table'[app_val])
RETURN
IF(
    SELECTEDVALUE('Table'[App_Month]) = 1,
    0,
    DIVIDE(_currentValue - _prevMonthValue, _prevMonthValue)
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi M_SBS_6 

     

    I already understand your needs:

    What you want is to calculate the difference between each month and the previous month as a percentage

    The solution is as follow:

    First, I created a table using your data,

    A calculated column is then created to calculate the gap:

    --------------------------------------------------------------------------------

    app_variance =

    var A = CALCULATE(

                SUM('Table'[App_Val]),

                FILTER(

                    'Table',

                    'Table'[App_Month] = EARLIER('Table'[App_Month]) - 1

                )

            )

    var B = ('Table'[App_Val]-A)/A

    RETURN IF(A<>0,B)

    --------------------------------------------------------------------------------
    A is used to obtain the data of the previous month.

    B is used to get the difference.

    Then use the format in the Column tools to change the format of the data to the form of percentages:

    The result is as followed:

    • M_SBS_6's avatar
      M_SBS_6
      Icon for Helper V rankHelper V
      2 years ago

      Thanks Anonymous I have followed your steps but unfortunately, I still don't get the right output. Because I have multiple years, the variance % is summing the percentage values. If I don't summarise, I get multiple records back in the table. 

       

      I'm wondering if a group by needs to be added into this, any idea how we could look to incorporate this at all please? Group by app_month.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi M_SBS_6 

        I understand your problem:

        Since you have more than one year in your table, filtering by month will add the values from the different years together.

        Here is my solution:

        I used the date column instead of the month column, and I also added some dates from different years for testing.

        Then I created a calculated column:

        app_Variance = 

var A = IF(MONTH('Table'[App_Date])<>1,
//It is used to determine when the date falls in January of each year
            CALCULATE(
                SUM('Table'[App_Val]),
                FILTER(
                    'Table',
                    MONTH('Table'[App_Date]) = MONTH(EARLIER('Table'[App_Date])) -1
                    &&
                    YEAR('Table'[App_Date]) = YEAR(EARLIER('Table'[App_Date]))
                )
//When the month is not in January, the Val value of the previous month is extracted,
            ),
            CALCULATE(
                SUM('Table'[App_val]),
                FILTER(
                    'Table',
                    MONTH('Table'[App_Date]) = 12
                    &&
                    YEAR('Table'[App_Date]) = YEAR(EARLIER('Table'[App_Date]))-1
                )
        )
//When it is in January, the Val value of December of the previous year is extracted
)
var B = ('Table'[App_Val]-A)/A
//compute
RETURN IF(A<>0,B)

        The result is as follow:

        Best Regards,

        Zhengdong Xu

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