Today's date for each year

[SilviyaK]

Hi all,

I need help with creating a chart showing sales for all years until now (2019 to 2023) but only up to today's date. Basically as we are 7th of March today, I will need to see sales for 2023 until 7th of March 2023, for 2022 until 7th of March 2022, for 2021 until 7th of March 2021 and so on... Tomorrow it will have to change to 8th of March for all years and etc.

It feels very simple, but I am actually having a very hard time thinking of a logic that I can implement in the script.

Any ideas? 

[E_Røse · Accepted solution]

create a field in the loadscript using

daynumberofyear(date) as daynumberofyear,

and a variable

Let vDaynumberofyearToday = daynumberofyear(today());

and  use a set expression like:

{$<daynumberofyear={"<=vDaynumberofyearToday"}>}

Alternatively, just create field with a flag in the load script

if(daynumberofyear(date)<=daynumerofyear(today()), 1,0) as [daynumber_flag] and filter on the flag. 

This is the probably more efficient than the first alternative, if you don't need to know the daynumber.

Please like and mark my answer as a solution, if it resolved your issue.

[Or]

I forget if there's a function to do this directly - there probably is and I just can't remember - but you could use something like

InYearToDate(Date,MakeDate(Year(Date))) as IsYTDFlag

And then use that flag in your set analysis.

[SilviyaK]

Thanks both! 

I tried with Elin's solution because I saw it first and it worked perfectly!