Hello Everybody!
I'm having some problems comparing prices of diferent years and using filters to show the savings in the current year.
The Matematic equation is: Saving = (Price Paid - Average Price Last Year)*Quantity of this year
I used this equation for the Average price of last year:
Price2019 = Sum({$<Purchase_Date={">=01/01/2019 <=31/12/2019"}>}Purchase_value)/Sum({$<Purchase_Date={">=01/01/2019 <=31/12/2019"}>}Purchase_Quantity)
And this other equation to current year price:
Price2020 = Sum({$<Purchase_Date={">=01/01/2020}>}Purchase_value)/Sum({$<Purchase_Date={">=01/01/2020}>}Purchase_Quantity)
Then
Saving = (Price2020 - Price2019)*Purchase_Quantity
But, when I apply the filter "Purchase_Date" to keep up with the current saving on the chart or table, an error occurs.
The image below is without date filter
Saving Calculation - No Date selected
This other image has a date filter for 2020
Saving Calculation - 2020 Selected Date
Could anybody help me?