Forum Discussion
Resolver IDifference 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 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!
8 Replies
JoHo_BIResponsive Resident
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_TechThank 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
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-msftMicrosoft Employee
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