Forum Discussion

GiorgosPap311's avatar
GiorgosPap311
Regular Visitor
2 years ago
Solved

Different Values Depending On Date Hierarchy

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

  • xifeng_L's avatar
    xifeng_L
    2 years ago

    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~

     

2 Replies

  • xifeng_L's avatar
    xifeng_L
    Super User
    2 years ago

    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~

     

    • GiorgosPap311's avatar
      GiorgosPap311
      Regular Visitor
      2 years ago

      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!