Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Groupings using date and current date and year

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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"
    )
v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

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
6 years ago

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
    6 years ago

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