Forum Discussion

_Aleksa_'s avatar
_Aleksa_
Helper II
6 years ago
Solved

Dynamic SWITCH TIME Statement

Hello,   I need help making the second part of the statement below dynamic so that it reflects current year appropriately as time goes by.   Tiers = SWITCH (     TRUE (),     'Weekly Data'[I...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try this calculated column:

    Tiers = 
IF (
    YEAR ( 'Table'[Date] )
        = YEAR ( TODAY () - 3 ),
    "CY",
    "PY"
        & DATEDIFF ( 'Table'[Date], TODAY () - 3, YEAR )
)

    The result shows:

    See my attached pbix file.

     

    Best Regards,

    Giotto

_Aleksa_'s avatar
_Aleksa_
Helper II
6 years ago

I am trying to avoid hardcoding any years like in the example below.

 I want to put a formula instead of an actual year which will provide a respective yearso that I don't have to updates manually the years in the statement every year.

 

Tiers =
SWITCH (
    TRUE (),
    'Weekly Data'[Injury_Dte] = 2020, 'CY'
                'Weekly Data'[Injury_Dte] = 2019, 'PY 1'
     'Weekly Data'[Injury_Dte]) = 2018, 'PY 2'
                .....
">PY6"

)

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

Please try this calculated column:

Tiers = 
IF (
    YEAR ( 'Table'[Date] )
        = YEAR ( TODAY () - 3 ),
    "CY",
    "PY"
        & DATEDIFF ( 'Table'[Date], TODAY () - 3, YEAR )
)

The result shows:

See my attached pbix file.

 

Best Regards,

Giotto