Forum Discussion

bjsrm8's avatar
bjsrm8
Frequent Visitor
7 years ago
Solved

Create Measure to find Month Variance

Hello,

 

I'm trying to use more Power BI in my daily use. I have a striaght forward use case but I need help exectuing. 

 

My data is structured and loads into PBI error free. Here is my data.

 

My Goal: Summarize tax by month and calculate a variance between most recent month and prior month. This is how it is current performed in excel. 

I've tried pivoting month and tax into their own columns based on other forum posts. This has mostly worked. My issue is I can not get the data to dynamically update for the next months data.

 

What would be the recommended steps to accomplish this? Should I continue with the pivoted columns or is there a better way to summarize this data. 

 

Any help would be appreciated!

Thanks!

Need Help
Tutorial Requests
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi bjsrm8 ,

     

    In Power BI, you could create measure to achive your desired output.

     

    Assuming that you have data sample like below.

     

    Code       Period                Tax

    471 February 2019 10
    471 February 2019 20
    471 February 2019 30
    471 March 2019 40
    471 March 2019 50
    471 March 2019 60
    472 February 2019 25
    472 February 2019 35
    472 February 2019 45
    472 March 2019 20
    472 March 2019 25
    472 March 2019 30

     

    Please create the measure below.

     

    thismonth =
CALCULATE (
    SUM ( Table1[Tax] ),
    FILTER (
        'Table1',
        'Table1'[Period].[MonthNo] = MONTH ( TODAY () )
            && 'Table1'[Code] = MAX ( 'Table1'[Code] )
    )
)

previous_month =
VAR thismonth =
    MONTH ( TODAY () )
VAR previous_month =
    IF ( thismonth = 1, 12, thismonth - 1 )
RETURN
    CALCULATE (
        SUM ( Table1[Tax] ),
        FILTER (
            'Table1',
            'Table1'[Period].[MonthNo] = previous_month
                && 'Table1'[Code] = MAX ( 'Table1'[Code] )
        )
    )

Difference = 'Table1'[thismonth]-'Table1'[previous_month]

    Here is the output.

     

     

    More details, please refer to my attachement.

     

    Best  Regards,

    Cherry

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi bjsrm8 ,

     

    In Power BI, you could create measure to achive your desired output.

     

    Assuming that you have data sample like below.

     

    Code       Period                Tax

    471 February 2019 10
    471 February 2019 20
    471 February 2019 30
    471 March 2019 40
    471 March 2019 50
    471 March 2019 60
    472 February 2019 25
    472 February 2019 35
    472 February 2019 45
    472 March 2019 20
    472 March 2019 25
    472 March 2019 30

     

    Please create the measure below.

     

    thismonth =
CALCULATE (
    SUM ( Table1[Tax] ),
    FILTER (
        'Table1',
        'Table1'[Period].[MonthNo] = MONTH ( TODAY () )
            && 'Table1'[Code] = MAX ( 'Table1'[Code] )
    )
)

previous_month =
VAR thismonth =
    MONTH ( TODAY () )
VAR previous_month =
    IF ( thismonth = 1, 12, thismonth - 1 )
RETURN
    CALCULATE (
        SUM ( Table1[Tax] ),
        FILTER (
            'Table1',
            'Table1'[Period].[MonthNo] = previous_month
                && 'Table1'[Code] = MAX ( 'Table1'[Code] )
        )
    )

Difference = 'Table1'[thismonth]-'Table1'[previous_month]

    Here is the output.

     

     

    More details, please refer to my attachement.

     

    Best  Regards,

    Cherry

    • bjsrm8's avatar
      bjsrm8
      Frequent Visitor
      7 years ago

      Thank you for helping me! I greatly appreciate it.