Monthly variance

[Anonymous]

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 

[Nathaniel_C]

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

[ryan_mayu · Accepted solution]

@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

[Anonymous]

Perfect! Thanks @ryan_mayu 

[ryan_mayu]

you are welcome