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'[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!!
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

    Appreciate your Kudos.

    • _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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    _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)