Forum Discussion

kevinko14's avatar
kevinko14
Regular Visitor
4 years ago
Solved

Calculate variance from previous date

Hi,

 

I have a table as below, so I would like to find out the production variance between 2 different reportdate. How can I do this in Power BI?

 

 

I created pivot table as example

 

I would like the end reuslt is like below.

Example:

Variance between reportdate 25/10/2021 and 26/10/2021 is 22-6=16

then variance between reportdate 26/10/2021 and 27/10/2021 is 23-22=1

 

This is what I created in PowerBI.

 

Thank you so much for your help.

 

Regards,

Kevin

 

Need Help
Show and Tell
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi kevinko14 

    Based on this example, the solution is 

    create the measures,

    Measure = 
    var _predate=CALCULATE(MAX('Table'[ReportDate]),FILTER(ALL('Table'),'Table'[ReportDate]<MAX('Table'[ReportDate])))
    var _prevalue=CALCULATE(SUM('Table'[Production]),FILTER(ALL('Table'),'Table'[ReportDate]=_predate&&'Table'[Date]=MAX('Table'[Date])))
return IF(ISBLANK(_predate),0,SUM('Table'[Production])-_prevalue)

    result

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

4 Replies