Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mamoormasoomi
Helper I
Helper I

Calcu;ate Last month vs. previous month variance in Matrix

Hello,

 

I am trying to calculate the variance between last month vs. previous month in Matix. The below is the sample data and the screen shot. How can I get the values in Matrix column for this last month vs. previous month difference.?

 

 

MOM Comparision.jpg

 

 

 

Eqp TypSvc DtCOUNTRYCost
Table2/22/2017US94
Table11/30/2016CA268
Table11/25/2016CA51
Table12/1/2016CA66
Chair11/8/2016US64
Table11/7/2016US68
Table7/8/2017US243
Table11/8/2016US68
Table10/18/2016US229
Table7/8/2017US68
Rug10/19/2016US68
Table10/19/2016US67
Table10/7/2016US3
Table8/18/2017US68
Table10/18/2016US68
Table9/14/2016US45
Table10/6/2016US65
Table8/25/2016US65
Table10/14/2016US52
Chair10/7/2016US236
Table10/19/2016CA66
Table10/19/2016CA21
Table10/19/2016CA47
Table10/20/2016CA66
Table10/20/2016CA66
Chair8/29/2016US132
Table7/10/2016MX26
Chair9/7/2016US88
Chair9/12/2016US23
Table10/12/2016US68
Table10/12/2016US68
Table5/4/2016US

110

 

10 REPLIES 10
v-yulgu-msft
Employee
Employee

Hi @mamoormasoomi,

 

Please try below measure:

Difference =
VAR PreviousMOnthTotal =
    CALCULATE (
        SUM ( 'difference between month'[Cost] ),
        FILTER (
            ALLEXCEPT ( 'difference between month', 'difference between month'[COUNTRY] ),
            'difference between month'[Svc Dt].[MonthNo]
                = MAX ( 'difference between month'[Svc Dt].[MonthNo] ) - 1
        )
    )
RETURN
    SUM ( 'difference between month'[Cost] ) - PreviousMOnthTotal

1.PNG

 

If you only want to show the different between the last two months, please try:

Difference_1 =
IF (
    MAX ( 'difference between month'[Svc Dt].[MonthNo] )
        <> CALCULATE (
            MAX ( 'difference between month'[Svc Dt].[MonthNo] ),
            ALL ( 'difference between month' )
        ),
    BLANK (),
    VAR PreviousMOnthTotal =
        CALCULATE (
            SUM ( 'difference between month'[Cost] ),
            FILTER (
                ALLEXCEPT ( 'difference between month', 'difference between month'[COUNTRY] ),
                'difference between month'[Svc Dt].[MonthNo]
                    = MAX ( 'difference between month'[Svc Dt].[MonthNo] ) - 1
            )
        )
    RETURN
        SUM ( 'difference between month'[Cost] ) - PreviousMOnthTotal
)

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Yuliana,

 

Thanks you so much for providing me the solution. You are a great help. i am learning this tool.  I am looking for the last month  vs. previous month variance only. I have two years ( from April 2016 to September 2017) data in table  This data will increase on monthly basis and next month I will have October Data in this table.

 

Primary Objective : I want only a measure that only check the data of last month vs. previous month ( like September 2017 - August 2017) and provbide the variance in one column with respective to country.

 

I tried your 2nd solution but I an having error message. please see below the screenshot and help.

 

Error Meesage.jpg

Yuliana,

 

Please disregard my previous message, I found the issue where I was making mistake.

 

But can you please help me to remove the "Total Cost"  Column and the Difference_1 column from the Matrix. Please see below the screenshot for your reference.Error Meesage1.jpg

Yuliana,

 

Please disregard my previous message, I found the issue where I was making mistake.

 

But can you please help me to remove the "Total Cost"  Column and the Difference_1 column from the Matrix. Please see below the screenshot for your reference.Error Meesage1.jpg

@mamoormasoomi

 

Just remove the last bracket ")"

 


Regards
Zubair

Please try my custom visuals

Zubair,

 

Thanks for the solution.

 

Would you please check my last message where i want to remove two columns from the matix.

@mamoormasoomi

 

Go to FORMAT>>Sub totals and Turn Off Column Sub totals

 

Photo2.png


Regards
Zubair

Please try my custom visuals

Zubair,

 

Thanks for the solution but how can I remove the Measure name " Difference_1" from each column and only show this "Difference_1" in the last column of Matrix.

 

2nd question is I am not getting the correct total as my data table has data from April 2016 to September 2017 and when the Measure check the month it it does not checking the year of the month that is why my calcution for variance (last month vs. previous ) is not correct.

 

Please help.

 

 

Hi @mamoormasoomi,

 

Unfortunately, it is not possible to remove the Measure name " Difference_1" from each column and only show this "Difference_1" in the last column of Matrix. As the matrix only supports TOTAL in the last column, above method is just a workaround to show difference values.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Zubair_Muhammad
Community Champion
Community Champion

Hi @mamoormasoomi

 

How about this MEASURE

 

Difference =
VAR PreviousMOnthTotal =
    CALCULATE (
        SUM ( TableName[Cost] ),
        PREVIOUSMONTH ( TableName[Svc Dt] ),
        ALLEXCEPT ( TableName, TableName[Svc Dt] )
    )
RETURN
    SUM ( TableName[Cost] ) - PreviousMOnthTotal

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.