How to integrate dynamic year in a YTD set analysis expression?

[BoXiangWang]

Hi all,

I want to allow the user to see the YTD of the Current Year (CY) and Previous Year (PY) in a table while allowing the user to use the filter pane to select what is the "current year". For example, if there is no selection in the filter pane, the default CY would be 2024 (as this question posted) and the PY would be 2023. Once the user selects 2023, the CY will be 2023 and the PY is 2022.

Here is the table with dummy data for reference:

I've created a variable that allows me to set the default as 2024, while once the user selects "year" in the filter pane the result will change accordingly. Here is the expression:

if(GetSelectedCount([Date.autoCalendar.Year])>0, GetFieldSelections([Date.autoCalendar.Year]), year(today()))

As of now, in the CY column, I wrote the expression below. Since the data set, I've set that the system only loads the latest 3 years of data. The latest date will always be today. So, the number in the table will change once the data set is reloaded.

count({<[Date.autoCalendar.Year]={'$(=$(v_Sel_Year))'}>} Flight_Number)

Here comes the question. In the PY column, I only want to show the same period as CY. However, what I see now shows the whole data in the previous year. I don't know how to adjust the set analysis to get what I want. The expression is below.

count({<[Date.autoCalendar.Year]={'$(=$(v_Sel_Year)-1)'}>} dim_fli_flight_leg_sk)

I've tried to use the YTD expression. But the thing is whenever I filter the year, the PY cannot change accordingly and it will show 0. Are there any recommendations? Thanks!

Let me add something more. At the moment, when I select the "2023" in the filter pane. I got this:

Once I remove the selection. The default will be 2024. It shows this:

What I want to achieve is the PY column only shows the YTD data as the CY column in 2024, when I select 2023 they still give me the same results as before (the whole year).

[Antoine04 · Accepted solution]

Hello,

Try something like this :

- CY : count({<Year={"$(=Year(RangeMin(Max(Date),Today())))"}>} Flight_Number) : that will count the flights for the minimal year between 2024 and the max year selected

- PY : count({<Year={"$(=Year(RangeMin(Max(Date),Today()))-1)"}>} Flight_Number) : that will count the flights for the year before the CY

- PYTD : count({<Year={"$(=Year(RangeMin(Max(Date),Today()))-1)"}, Date={"<=$(=AddYears(RangeMin(Max(Date),Today()),-1))"}>} Flight_Number) : that will count the flights for the year before the CY, but only for the date before today or before the last date known in the CY

Let me know if you need more infos.
Regards,

Antoine L

[BoXiangWang]

Hi Antoine,

Thanks for the solution. It indeed works!

Although in my dataset, the Max(Date) is smaller than the Today() in 2024, I changed the expression to RangeMax().

Here is what I use:

count({<[Date.autoCalendar.Year]={"$(=Year(RangeMin(Max(Date),Today()))-1)"}, [Date.autoCalendar.Date]={"<=$(=AddYears(RangeMax(Max(Date),Today()),-1))"}>} Amount)

This can give me the PYTD when I select 2024, or not selecting anything, which by default is also 2024. When I select 2023 or earlier, it will give me the whole year in the previous year. That is exactly what I want. I can only use one column to show the PYTD also the whole year of PY.

Here is a more elaborated example in my case. Just for reference:

Max(Date) = 2/20/2024 [M/D/Y]

Today() = 3/5/2024

$(=AddYears(RangeMax(Max(Date),Today()),-1)) = 

$(=AddYears(RangeMax(2/20/2024,3/5/2024),-1)) =

$(=AddYears(3/5/2024,-1)) =

3/5/2023

[BoXiangWang]

I have one further question. Since I already got 3/5/2023. Why can't I just use

count({< [Date.autoCalendar.Date]={"<=$(=AddYears(RangeMax(Max(Date),Today()),-1))"} >} Amount)

Why do I still need the first set analysis to indicate the specific Year?

The Date only retrieves the date? But the result also shows the year, which confuses me.