Hello everyone,
I am trying to compare year by year data depending on a date filter.
For example: I have 3 columns to compare 3 years 2018,2019,2020(calculated label). So if I select for example Jan-2020, I should get data for Jan-2020 in the 2020 column, Jan-2019 for the 2019 column and Jan-2018 for 2018 column.
Same goes for if I select multiple Months , Jan-2020, Feb-2020 then 2020 should have Jan-2020 to Feb-2020 and so on for other columns.
This works the other way as well. If I pick Jan-2019 then the comparison would be between 2019,2018,2017.
for 2 years prior I use
=Sum({<ServiceDate = {">=$(=MonthStart(AddYears(Max(ServiceDate), -2)))<=$(=AddYears(Max(ServiceDate), -2))"}>} Volume)
For 1 year prior
=Sum({<ServiceDate = {">=$(=MonthStart(AddYears(Max(ServiceDate), -1)))<=$(=AddYears(Max(ServiceDate), -1))"}>} Volume)
For current
=sum({<ServiceDate={">=$(=MonthStart(Max(ServiceDate)))<=$(=Max(ServiceDate))"}>}[Volume])
This works well until I select a filter. If I pick Jan-2020 from the data filter the current year column displays correct data whereas the other 2 columns shows zero.
What am I doing wrong?
