Forum Discussion

Unicorn_Tech's avatar
Unicorn_Tech
Icon for Resolver I rankResolver I
8 years ago

Difference of values between two dates

If I have a finished report, that is showing the country totals for 2016 and 2017 - is there an easy way to calcuate the difference (increase sum) between the two?   The source table is a list of a...
Need Help
JoHo_BI's avatar
JoHo_BI
Icon for Responsive Resident rankResponsive Resident
8 years ago

Hi Unicorn, 

 

Try this:

Measure = CALCULATE(SUM(Table1[Value]),Year(Table1[Date]) = YEAR(TODAY())) - CALCULATE(SUM(Table1[Value]),Year(Table1[Date]) = (YEAR(TODAY()) - 1))

Changing the table/column names to your need. 

 

This example keeps it dynamic too, looking at the year of today and always getting last year. You can always change these values to hard code them in. 

 

Hope that helps!

  • Unicorn_Tech's avatar
    Unicorn_Tech
    Icon for Resolver I rankResolver I
    8 years ago

    Thank you so much!  I'm going to try that tomorrow and let you know.  That looks exactly like what I'm looking for.

  • Unicorn_Tech's avatar
    Unicorn_Tech
    Icon for Resolver I rankResolver I
    8 years ago

    Hi.  So I tried it, and unfortunately I got an error. This was my code:

    2017 extension = calculate(SUM('Contract Line History'[Contract Lines - Converted Ha]),Year('Contract Line History'[new_Date] = Year(today())) - calculate(Sum('Contract Line History'[Contract Lines - Converted Ha]), Year('Contract Line History'[new_Date]) - (Year(Today()) - 1)))

     

    The error I got was "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression.  This is not allowed."

     

    Would a way to resolve this be to remove the "Today" features, and replace it with the exact year?

    • Unicorn_Tech's avatar
      Unicorn_Tech
      Icon for Resolver I rankResolver I
      8 years ago

      Hi JoHo,

       

      So this was my formula, and while it didn't return an error, it also didn't return any values either.:

       

      2017 extension = calculate(SUM('Contract Line History'[Contract Lines - Converted Ha]),Year('Contract Line History'[new_Date] = 2017)) - calculate(Sum('Contract Line History'[Contract Lines - Converted Ha]), Year('Contract Line History'[new_Date]) - (Year(2016)))

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Unicorn_Tech,

         

        Could you try the formula below to see if it works in your scenario? :smileyhappy:

        2017 extension =
CALCULATE (
    SUM ( 'Contract Line History'[Contract Lines - Converted Ha] ),
    FILTER (
        ALL ( 'Contract Line History' ),
        YEAR ( 'Contract Line History'[new_Date] ) = 2017
    )
)
    - CALCULATE (
        SUM ( 'Contract Line History'[Contract Lines - Converted Ha] ),
        FILTER (
            ALL ( 'Contract Line History' ),
            YEAR ( 'Contract Line History'[new_Date] ) = 2016
        )
    )

         

        Regards