Calculate difference between data of a 'baseline/reference' year/month and later year/month

[MartijnB]

Hi guys,

This is my first message in the Power BI community.

I have imported a SharePoint list which contains energy consumption figures (like: gas, electricity, etc.) per site, per month, as per 2013 till now. Now I am looking for a calculation which calculates the difference between the figures of a 'baseline/reference' year/month, with figures of the same month of a later year, so for example:

• Jan - 2013 vs. Jan - 2017
• Apr - 2013 vs. Apr - 2018 and so on

I can create a year over year comparison, but I don’t know how to ‘fix’ (like the $-$ in Excel) the baseline year. I tried Dateadd, but the number of years will vary (2014 vs. 2013 = -1, but 2018 vs. 2013 = -5). Furthermore, I was also thinking about creating a separate table for the baseline year, but then I didn’t got the time intelligence to work.

I hope you can help me, thanks!

Regards,

Martijn

Printscreen of dataset

[v-cherch-msft]

Hi @MartijnB 

You may try to create the two measures like below.For example:

2013 = CALCULATE(SUM(Data[value]),FILTER(Data,YEAR(Data[date])=2013))

2018 =
CALCULATE (
    SUM ( Data[value] ),
    FILTER (
        ALL ( Data ),
        YEAR ( Data[date] )
            = YEAR ( MAX ( Data[date] ) ) + 5
            && MONTH ( Data[date] ) = MONTH ( MAX ( Data[date] ) )
    )
)

Regards,

[MartijnB · Accepted solution]

Hi Cherie,

Thanks for your reply! Unforunatly I couldn't get your two measures to work, but I found an other solution via the seperate 2013 dataset I created. Via 'Lookupvalue' I get the 2013 values in the original dataset per site and month. After that, it was easy to create the difference calculation.

So issue solved. Thanks for your help!

[v-cherch-msft]

Hi @MartijnB 

Glad to hear you've solved it, please accept the reply as solution to close this thread.

Regards,