Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
i have a variable in script:
let vPriorBookingDate = 'date(addyears(SP_BookingDate,-1))'; to make a previous year date and in a straight table
i have dimension SP_Booking date and in expression
Dimension | sum for current date | sum for previous year date |
SP_BookingDate ( 2021-07-26) | 123 | 123 |
I want to with sum({$<SP_BookingDate={"=$(vPriorBookingDate)"}>}SP_ParticipantsCount) sum values in 2020-07-26 but it shows exact same values as in current booking date
is there any possibility to do it correctly?
Thanks for all help and have a nice day!
Do not try and use set analysis to set a dimension value for a dimension in a table (SP_BookingDate).
1) You can create a previous year metric in the data model associated to the current date
ex.
left join (factTable)
load
addyears(SP_BookingDate,1) as SP_BookingDate
sum(field) as PreviousYearMetric
resident factTable
;
2) or you can create a field that isn't qualified by year ("DayMonth"), so records for 2021, 2020, 2019 etc can all have the same value for DayMonth, ex. 07-26. Then in your front-end you can use set analysis for Year = $(currentYear) and Year = $(previousYear).