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
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. :)

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

        So I've created a new table that summarizes the values per year (per region, country). 

        2017 Difference = Calculate(SUM('Extension Table'[TOTAL]), 'Extension Table'[Year] = 2017 - calculate(SUM('Extension Table'[TOTAL]), 'Extension Table'[Year] = 2016))

        So now I'm back to the error of "A function calculate has been used in a True/False expresssion that is used as a table filter expression."