Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have month wise data of certain expenses. I have plotted this data in Matrix as expenses in rows and months in columns. I have already activated columns subtotal from visual format which is giving me total value of expense for all the months, but I need only difference of last two months in subtotal column or I need one additional column at the end of all months, showing difference of last two months I have attached images for both currently I am having and image for what I need, Does it possible in DAX or another function??
Solved! Go to Solution.
@Vinayak1991
You can follow this pattern:
LastTwoMonhsdiff =
Var __relation = SUMMARIZE(ALL(financials), financials[Month Name], financials[Month Number])
Var __maxMonthNum = MAXX(__relation, financials[Month Number])
Var __lastToLastMonth = SUMX(FILTER( __relation, financials[Month Number] = __maxMonthNum - 1), CALCULATE(SUM(financials[ Sales])))
Var __lastMonth = SUMX(FILTER( __relation, financials[Month Number] = __maxMonthNum ) , CALCULATE(SUM(financials[ Sales])))
RETURN IF(HASONEVALUE(financials[Month Name]), CALCULATE(SUM(financials[ Sales])), __lastMonth - __lastToLastMonth )
I think the new visual calculations feature will make this pattern quiet simple.
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun
@Vinayak1991
You can follow this pattern:
LastTwoMonhsdiff =
Var __relation = SUMMARIZE(ALL(financials), financials[Month Name], financials[Month Number])
Var __maxMonthNum = MAXX(__relation, financials[Month Number])
Var __lastToLastMonth = SUMX(FILTER( __relation, financials[Month Number] = __maxMonthNum - 1), CALCULATE(SUM(financials[ Sales])))
Var __lastMonth = SUMX(FILTER( __relation, financials[Month Number] = __maxMonthNum ) , CALCULATE(SUM(financials[ Sales])))
RETURN IF(HASONEVALUE(financials[Month Name]), CALCULATE(SUM(financials[ Sales])), __lastMonth - __lastToLastMonth )
I think the new visual calculations feature will make this pattern quiet simple.
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun