Calculate Variance in PowerBI using dax query

Question

I am trying to create a variance measure in PowerBI.

This is the data that I have,

Month Year MonthNo Value 
Jan    2016  1      700  
Feb    2016  2      800
March  2016  3      900
April  2016  4      750
.
.
Jan    2017 13      690
Feb    2017 14      730

And My variance for the Month Number 7 should be like,

`{Avg(values(4,5,6) - Value(7)} / Value(7)`
i.e (Average of last 3 months value - current month value) / Current month value

How to do this in Power BI? Thanks.

Marc Pincince Marc Pincince · Accepted Answer · 2017-09-01T12:21:22

If it is okay for you to use a column, I believe you could add one with this code to get what you want:

Variance = (CALCULATE(AVERAGEX(Sheet1,Sheet1[Value]),FILTER(FILTER(Sheet1,Sheet1[MonthNo]<=EARLIER(Sheet1[MonthNo])-1),Sheet1[MonthNo]>=EARLIER(Sheet1[MonthNo])-3))-Sheet1[Value])/Sheet1[Value]

You'll need to replace all instances of Sheet1 with the name of your table.

It'll give you something like this:

Calculate Variance in PowerBI using dax query

1 Answers