Groupings using date and current date and year

[v-frfei-msft]

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]

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

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