Unable to to sum based on current year vs last year to date

[Anonymous]

Hi, I am new to QS. I have data for year 2017 & 2018 in a table. what i am try to do is take SUM of all transactions up to TODAY but only for current year. I am trying to prepare comparison of current YTD vs Last YTD .

I have tied all sort of YTD & LY TD formulas Date Level Analysis - WTD, MTD, QTD & YTD (Current Year & Previous Year) but none seems to be working

Attached is my detailed ! .QVF file

[sunny_talwar · Accepted solution]

Is this the goal?

Expressions

=Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE))) & '<=' & Date(Max(DATE)))"}>} [Total Avail Room])

=Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE))) & '<=' & Date(Max(DATE)))"}, MPECode={'1. JA JABH'}>}[Total Avail Room])

=Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE), -1)) & '<=' & Date(AddYears(Max(DATE), -1)))"}>}[Total Avail Room])

=Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE), -1)) & '<=' & Date(AddYears(Max(DATE), -1)))"}, MPECode={'1. JA JABH'}>}[Total Avail Room])

[Anonymous]

Thanks a lot, you are a star

[Anonymous]

just for my understanding.

=Sum({<DATE = {"$(='>=' & Date(YearStart(Max(DATE))) & '<=' & Date(Max(DATE)))"}>} [Total Avail Room])

DATE (capital) is my date field? and I can also replace it with my auto calendar field as below?

=Sum({<[Date.autoCalendar.Date] = {"$(='>=' & Date(YearStart(Max([Date.autoCalendar.Date]))) & '<=' & Date(Max([Date.autoCalendar.Date])))"}>} [Total Avail Room])

[sunny_talwar]

I am not 100% confident, have you tested to see if it works or not?

[Anonymous]

its not working with autoCalendar.Data

[sunny_talwar]

Then I guess it is a problem related to derived fields