Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Groupings using date and current date and year

  • Hi Anonymous ,

     

    To update the formula as below:

     

    Type =
    VAR last1 =
        EDATE ( TODAY (), -12 )
    VAR last2 =
        EDATE ( TODAY (), -24 )
    VAR last3 =
        EDATE ( TODAY (), -36 )
    VAR last4 =
        EDATE ( TODAY (), -48 )
    VAR last5 =
        EDATE ( TODAY (), -60 )
    VAR last6 =
        EDATE ( TODAY (), -72 )
    VAR last7 =
        EDATE ( TODAY (), -84 )
    VAR last8 =
        EDATE ( TODAY (), -96 )
    VAR last9 =
        EDATE ( TODAY (), -108 )
    VAR last10 =
        EDATE ( TODAY (), -120 )
    RETURN
        SWITCH (
            TRUE (),
            'Table'[RegDate] <= TODAY ()
                && 'Table'[RegDate] >= last1, "yr1",
            'Table'[RegDate] < last1
                && 'Table'[RegDate] >= last2, "yr2",
            'Table'[RegDate] < last2
                && 'Table'[RegDate] >= last3, "yr3",
            'Table'[RegDate] < last3
                && 'Table'[RegDate] >= last4, "yr4",
            'Table'[RegDate] < last4
                && 'Table'[RegDate] >= last5, "yr5",
            'Table'[RegDate] < last5
                && 'Table'[RegDate] >= last6, "yr6",
            'Table'[RegDate] < last6
                && 'Table'[RegDate] >= last7, "yr7",
            'Table'[RegDate] < last7
                && 'Table'[RegDate] >= last8, "yr8",
            'Table'[RegDate] < last8
                && 'Table'[RegDate] >= last9, "yr9",
            'Table'[RegDate] < last9
                && 'Table'[RegDate] >= last10, "yr10"
        )
    

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support

    Hi Anonymous ,

     

    To create a calculated column as below.

    Type = 
    VAR last1 =
        EDATE ( TODAY (), -12 )
    VAR last2 =
        EDATE ( TODAY (), -24 )
    RETURN
        IF (
            'Table'[RegDate] <= TODAY ()
                && 'Table'[RegDate] >= last1,
            "yr1",
            IF ( 'Table'[RegDate] < last1 && 'Table'[RegDate] >= last2, "yr2", "yr3" )
        )
    

    Pbix as attached.

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi thanks for the reply. But if i want to include all the way to >Yr10 how do i do it? sorry very new to power bi. 

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support

        Hi Anonymous ,

         

        To update the formula as below:

         

        Type =
        VAR last1 =
            EDATE ( TODAY (), -12 )
        VAR last2 =
            EDATE ( TODAY (), -24 )
        VAR last3 =
            EDATE ( TODAY (), -36 )
        VAR last4 =
            EDATE ( TODAY (), -48 )
        VAR last5 =
            EDATE ( TODAY (), -60 )
        VAR last6 =
            EDATE ( TODAY (), -72 )
        VAR last7 =
            EDATE ( TODAY (), -84 )
        VAR last8 =
            EDATE ( TODAY (), -96 )
        VAR last9 =
            EDATE ( TODAY (), -108 )
        VAR last10 =
            EDATE ( TODAY (), -120 )
        RETURN
            SWITCH (
                TRUE (),
                'Table'[RegDate] <= TODAY ()
                    && 'Table'[RegDate] >= last1, "yr1",
                'Table'[RegDate] < last1
                    && 'Table'[RegDate] >= last2, "yr2",
                'Table'[RegDate] < last2
                    && 'Table'[RegDate] >= last3, "yr3",
                'Table'[RegDate] < last3
                    && 'Table'[RegDate] >= last4, "yr4",
                'Table'[RegDate] < last4
                    && 'Table'[RegDate] >= last5, "yr5",
                'Table'[RegDate] < last5
                    && 'Table'[RegDate] >= last6, "yr6",
                'Table'[RegDate] < last6
                    && 'Table'[RegDate] >= last7, "yr7",
                'Table'[RegDate] < last7
                    && 'Table'[RegDate] >= last8, "yr8",
                'Table'[RegDate] < last8
                    && 'Table'[RegDate] >= last9, "yr9",
                'Table'[RegDate] < last9
                    && 'Table'[RegDate] >= last10, "yr10"
            )