Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    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

Nathaniel_C's avatar
Nathaniel_C
Community Champion
4 years ago

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