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 ...
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.

v-xiaotang's avatar
v-xiaotang
Community Support
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.

  • kevinko14's avatar
    kevinko14
    Regular Visitor
    4 years ago

    v-xiaotang It works, thank you so much.