Forum Discussion

MartijnB's avatar
MartijnB
Regular Visitor
7 years ago
Solved

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

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

Need Help
  • MartijnB's avatar
    MartijnB
    7 years ago

    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!

3 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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's avatar
      MartijnB
      Regular Visitor
      7 years ago

      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's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi MartijnB 

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

        Regards,