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

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

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

        Hi.  I think I would like to try that, but I've realized a problem.  My table has each product ordered, and the year they ordered it.  I actually want to sum by Country, and then understand the difference between the sum of each year.  So actually, what I would need to do is a seperate table that just has 2016 and 2017 sums per each country, and then try a calculate formula like the one above.

         

        I'm going to spend some time today trying to figure out how to do that. :)