Sum participants with set expression

[Woojtek_96]

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!

[bgl-sjoyce]

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).