Get year from Time dimension

Questions & Answers
,
1

Hi Community,
I need to add an Ecstatic value to a metric, this value will change over the years, this is my code:

CASE WHEN [Time].[Year].CurrentDateMember  < 2022 THEN
2748
ELSE
5000
END

I noticed it was not working and I wanted to check what the year value was, I’m showing it in the following screenshot on the last column, value ‘1,206’. I hoped to see ‘2021’ in the first 3 rows, and then ‘2022’.
image
How can I do that?

Thanks in advance.

2

Hi @Daniel_Luevano,

The CurrentDateMember construct always returns the Time dimension level member respective to the current date. So for the “Year” level, it will be 2022 for any Time dimension member in the report rows. You want to use the CurrentMember instead.

But that will bring you only halfway to the desired result because it will return the member, not the year value. To get the numerical value of the year, add Key at the end. So, in conclusion, the condition could look similar to the one below:

CASE WHEN [Time].[Year].CurrentMember.Key  < 2022 THEN
...

See more details on the differences between the two on the eazyBI documentation page - CurrentDateMember and CurrentMember.

Best,
Roberts // support@eazybi.com

3

Roberts,
That metric is not returning the Year as expected:

image
image1218×1012 141 KB

Therefore the condition is always 2748.

4

Hi @Daniel_Luevano ,

For different Time dimension level members in the report, try the Ancestor() function. It will return a particular level member that is ancestral to the current one. The formula then could look similar to the one below:

Ancestor([Time].CurrentMember, [Time].[Year]).Key

See more details on the Ancestor() function here - Ancestor.

Best,
Roberts // support@eazybi.com

1 Like
5

Great, that’s what I was looking for, thanks.