Forum Discussion
Monthly variance
Hi everyone
Need help... I am new to DAX and trying to calculate the monthly variance for a graph. Would like to be able to do this calculation in PBI itself. Currently it's being done on excel before the file is imported.
I'd like to create a column like the one highlighted in yellow:
Thank you
Anonymous
you can create a date column and create a variance column.
date = date(left('Table'[Year month],4),RIGHT('Table'[Year month],2),1) Column = VAR _last=maxx(FILTER('Table','Table'[platfrom]=EARLIER('Table'[platfrom])&&'Table'[date]=EDATE(EARLIER('Table'[date]),-1)),'Table'[coverage]) return if(ISBLANK(_last),BLANK(),('Table'[coverage]-_last))pls see the attachment below
4 Replies
Anonymous
you can create a date column and create a variance column.
date = date(left('Table'[Year month],4),RIGHT('Table'[Year month],2),1) Column = VAR _last=maxx(FILTER('Table','Table'[platfrom]=EARLIER('Table'[platfrom])&&'Table'[date]=EDATE(EARLIER('Table'[date]),-1)),'Table'[coverage]) return if(ISBLANK(_last),BLANK(),('Table'[coverage]-_last))pls see the attachment below
Hi Anonymous ,
Please try this:Variance = VAR _curDate = MAX ( myTable[YearMonth] ) VAR _curPlatform = MAX ( myTable[Platform] ) VAR _prevDate = MAX ( myTable[YearMonth] ) - 1 VAR _curValue = MAX ( myTable[Value] ) VAR _minDate = CALCULATE ( MIN ( myTable[YearMonth] ), ALL () ) VAR _prevValue = CALCULATE ( MAX ( myTable[Value] ), FILTER ( ALL ( myTable ), myTable[YearMonth] = _prevDate && myTable[Platform] = _curPlatform ) ) VAR _variance = _prevValue - _curValue RETURN IF ( _minDate = MAX ( myTable[YearMonth] ), 0, _variance )
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
