Calculate variance from previous date

[kevinko14]

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

[v-xiaotang · Accepted solution]

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]

@v-xiaotang It works, thank you so much.

[amitchandak]

@kevinko14 , refer if this can help

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[kevinko14]

@amitchandak 

Thanks for your video. Sorry that I confused you, I would like to find out variance of production from two different dates 

for example:

Cell B19 is variance between 25/10/2021 and 26/10/2021, formula B6-B5=22-6=16

Cell B20 is variance between 26/10/2021 and 27/10/2021, formula B7-B6=23-22=1

How can I do this in powerbi?

Thank you so much.

Kevin