Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.?
Eqp Typ | Svc Dt | COUNTRY | Cost |
Table | 2/22/2017 | US | 94 |
Table | 11/30/2016 | CA | 268 |
Table | 11/25/2016 | CA | 51 |
Table | 12/1/2016 | CA | 66 |
Chair | 11/8/2016 | US | 64 |
Table | 11/7/2016 | US | 68 |
Table | 7/8/2017 | US | 243 |
Table | 11/8/2016 | US | 68 |
Table | 10/18/2016 | US | 229 |
Table | 7/8/2017 | US | 68 |
Rug | 10/19/2016 | US | 68 |
Table | 10/19/2016 | US | 67 |
Table | 10/7/2016 | US | 3 |
Table | 8/18/2017 | US | 68 |
Table | 10/18/2016 | US | 68 |
Table | 9/14/2016 | US | 45 |
Table | 10/6/2016 | US | 65 |
Table | 8/25/2016 | US | 65 |
Table | 10/14/2016 | US | 52 |
Chair | 10/7/2016 | US | 236 |
Table | 10/19/2016 | CA | 66 |
Table | 10/19/2016 | CA | 21 |
Table | 10/19/2016 | CA | 47 |
Table | 10/20/2016 | CA | 66 |
Table | 10/20/2016 | CA | 66 |
Chair | 8/29/2016 | US | 132 |
Table | 7/10/2016 | MX | 26 |
Chair | 9/7/2016 | US | 88 |
Chair | 9/12/2016 | US | 23 |
Table | 10/12/2016 | US | 68 |
Table | 10/12/2016 | US | 68 |
Table | 5/4/2016 | US | 110
|
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
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 )
Best regards,
Yuliana Gu
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.
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.
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.
Just remove the last bracket ")"
Zubair,
Thanks for the solution.
Would you please check my last message where i want to remove two columns from the matix.
Go to FORMAT>>Sub totals and Turn Off Column Sub totals
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |