topic Re: Different Values Depending On Date Hierarchy in DAX Commands and Tips

Different Values Depending On Date Hierarchy

GiorgosPap311 — Fri, 24 May 2024 22:04:39 GMT

I have a very easy (I suppose) question, but it's driving me crazy.
We have a table named "Dates" with 1 single column named "Date", let's say this one

Dates = CALENDAR("2020-01-01", "2024-01-01")

I want to create a measure and plot it on a bar chart with the following rule.

If the date hierarchy level selected is on Year level, then return 1
If the date hierarchy level selected is on Quarter level, then return 2
If the date hierarchy level selected is on Month level, then return 3
If the date hierarchy level selected is on Day level, then return 4

The reason to do so, is that if I have a way to create that, then I also have a way, using VARs, to create a bar chart that will use
PREVIOUSYEAR, PREVIOUSQUARTER, ..... dynamically depending on the level I'm currently on.

Thank you for your inputs

Re: Different Values Depending On Date Hierarchy

xifeng_L — Sat, 25 May 2024 03:53:47 GMT

Hi GiorgosPap311 ,

You can refer to below measure.

Measure = SWITCH(TRUE(), ISINSCOPE('Date'[Date]), 4 , ISINSCOPE('Date'[Month]), 3 , ISINSCOPE('Date'[Quarter]), 2 , ISINSCOPE('Date'[Year]), 1 )

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Different Values Depending On Date Hierarchy

GiorgosPap311 — Sat, 25 May 2024 13:29:05 GMT

I had already tried this measure, but only had 1 single column in my Dates table with the Date Hierarchy instead of using 4 different columns. I thought it should be possible only with 1 but it seems like it doesn't work the way I thought.
This works indeed, so thank you!