Dynamic SWITCH TIME Statement

[_Aleksa_]

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'[Injury_Dte] = year(today()-3), 'CY'

                'Weekly Data'[Injury_Dte] = year(today()-3)-1, 'PY 1'

     'Weekly Data'[Injury_Dte]) = year(today()-3)-2, 'PY 2'

                'Weekly Data'[Injury_Dte] = year(today()-3)-3 , 'PY 3'

                'Weekly Data'[Injury_Dte] = year(today()-3)-4 , 'PY 4'

                'Weekly Data'[Injury_Dte] = year(today()-3)-5 , 'PY 5'
">PY6"

)

Thank you in advance!!

[harshnathani]

@_Aleksa_ ,

You can try below measure

CCYR =

Var _date_ = MAX('Weekly Data'[Injury_Dte])

var _diff = DATEDIFF(_date_,TODAY(),YEAR)

RETURN

SWITCH(

TRUE(),

_diff = 0 , "CY",

_diff > 0 , CONCATENATE("PY",FORMAT(_diff,"##"))

)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[_Aleksa_]

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 · Accepted solution]

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