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.  ...
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

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.