Show data from date range of current and previous year

[Realmoca]

Hello everyone,

Lately I'm having this issue  whenever  someone selects  an specific date or date range from this year in any of  the charts from the dashboard that supposedly have to show the same data but from the previous year.

I don't know why but at the beginning it was working fine (only when I selected a year from the year filter), but but now it doesn't seems  work anymore.

ejemplo 1

The field that I'm using fot the Year filter is this one: =[his_date.autoCalendar.Year]

This is the expression that I'm using to calculate the values: 

=Sum ( {$<hist_y_otb={"History"}, [his_date.autoCalendar.Year]={$(#=Max(Year)-1)}>} quantity_rooms )

Also, when I don't have any filter selected it gaves me the desired result (2020 / 2019):

ejemplo 3

 In the other when I select only one month or one date, I get this result:

ejemplo 2

 I would like it to show the data from the same dates of the year/period selected and the previous year.  How can I accomplish this?

Thanks for the help!!

[srdheekonda]

I think as your filters are date level and your expression should be filtered at date level to solve your problem.

I tried some formulas at my end may be it will work for you. I applied date format to make my formula work in my case. apply date format according to your requirement.

another thing is CY YTD made to take dynamic values ($) and PY YTD applied constant selection (1).

Hope this helps:

CY YTD: 

SUM({$<his_date={">=$(=DATE(YearStart(Max(his_date)),'YYYY-MM-DD'))<=$(=Max(his_date))"}>} quantity_rooms)

PY YTD: 

SUM({1<his_date={">=$(=DATE(AddYears(YearStart(Max(his_date)),-1),'YYYY-MM-DD'))<=$(=DATE(AddYears(Max(his_date),-1),'YYYY-MM-DD'))"}>} quantity_rooms)

[Realmoca]

Hello  Srdheekonda,

Thank you very much for your prompt answer!

I tried with the recommendations that you gave me and it didn't seems to work. I don´t know why because I think that your approaching is the right one, it has to be related to data level and the expression that I'm using should be filtered at date level.

example 4

I don't know if it's important but I'm using the generated auto calendar fields to filter for year ([his_date.autoCalendar.Year]), quarter ([his_date.autoCalendar.Quarter]) and month ([his_date.autoCalendar.Month]) from the field his_date.

I have reviewed the forum and some websites and tried to use the recommendations and solutions, but I couldn't make it work. There is one the As of Table but I don't know how to apply it  😞

Please let me know if you need me to share anything more like more info.

Thanks!

[srdheekonda]

I think  you can use AutoCalendarDate  and format accordingly if there is any issues with Date Format.