Difference of values between two dates

[Unicorn_Tech]

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 all product purchases, the country of purchase and the year of purchase.  So right now, my report aggratees the sum per year per country.

Right now, I'm exporting to excel, and doing a somplu sum(2016-2017) for each country.  I'd like to keep this within Power BI.

Thank you!

[v-ljerr-msft]

Hi @Unicorn_Tech,

Could you share a sample pbix file(with just some sample/mock data) and your expected result, so that we can better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[JoHo_BI]

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]

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]

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]

Hi @Unicorn_Tech,

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

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]

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]

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."

[Unicorn_Tech]

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