Create new Date Dimension

[KPage]

Hi,

I would like to create a "Date" dimension that only uses the current year and the prior year WEEKS. Right now, I have data from 4 years ago. "Date" is already a dimension on my report that pulls by Week, but is there a formula I can use so that a separate "Date" dimension only pulls CY Weeks and PY Weeks?

Weeks go beyond 2023 in the table below. I also do not want to set a filter

Thank you

[Oleg_Troyansky]

Hi @KPage 

If you have access to the data load script, then the recommended approach would be to create a master calendar table in your data set, and to calculate various conditional flags in the calendar. One of the flags could validate the condition that the date belongs to the current year or the prior year. Let's call it CurrentPriorYear_Flag, for example. Then, in your chart expressions, you could request that the flag is equal to 1, like this:

Sum({<CurrentPriorYear_Flag={1}>}   Value)

or in this format:

{<CurrentPriorYear_Flag={1}>}  Sum(Value)

or even like this:

{<CurrentPriorYear_Flag={1}>} [Master Measure 1]

This way, you can ensure consistent performance, even on larger data sets. 

The solution that you asked for, that includes a conditional calculation in a calculated dimension, is highly wasteful, and would only work on small data sets. If you REALLY want to implement something like this, you could use the following formula in your calculated dimension:

AGGR( only( {<set analysis condition that limits the date to 2 years>} Date), Date)

Again, I would not recommend using this solution if your data set is big enough. It will slow things down quite a bit.

Cheers,