Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Grouping dates ranges

Need Help

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Sorry for misunderstanding you in your previous post, Please update the formula as below.

     

    Age mth to mth 2 =
VAR yeartoday =
    YEAR ( TODAY () )
VAR last1 =
    DATE ( yeartoday, 1, 1 )
VAR last2 =
    DATE ( yeartoday - 1, 1, 1 )
VAR last3 =
    DATE ( yeartoday - 2, 1, 1 )
VAR last4 =
    DATE ( yeartoday - 3, 1, 1 )
VAR last5 =
    DATE ( yeartoday - 4, 1, 1 )
VAR last6 =
    DATE ( yeartoday - 5, 1, 1 )
VAR last7 =
    DATE ( yeartoday - 6, 1, 1 )
VAR last8 =
    DATE ( yeartoday - 7, 1, 1 )
VAR last9 =
    DATE ( yeartoday - 8, 1, 1 )
VAR last10 =
    DATE ( yeartoday - 9, 1, 1 )
VAR last11 =
    DATE ( yeartoday - 10, 1, 1 )
RETURN
    SWITCH (
        TRUE (),
        'LDet'[Regdate] <= TODAY ()
            && 'LDet'[Regdate] >= last1, "Yr 1",
        'LDet'[Regdate] < last1
            && 'LDet'[Regdate] >= last2, "Yr 2",
        'LDet'[Regdate] < last2
            && 'LDet'[Regdate] >= last3, "Yr 3",
        'LDet'[Regdate] < last3
            && 'LDet'[Regdate] >= last4, "Yr 4",
        'LDet'[Regdate] < last4
            && 'LDet'[Regdate] >= last5, "Yr 5",
        'LDet'[Regdate] < last5
            && 'LDet'[Regdate] >= last6, "Yr 6",
        'LDet'[Regdate] < last6
            && 'LDet'[Regdate] >= last7, "Yr 7",
        'LDet'[Regdate] < last7
            && 'LDet'[Regdate] >= last8, "Yr 8",
        'LDet'[Regdate] < last8
            && 'LDet'[Regdate] >= last9, "Yr 9",
        'LDet'[Regdate] < last9
            && 'LDet'[Regdate] >= last10, "Yr 10",
        'LDet'[Regdate] < last10
            && 'LDet'[Regdate] >= last11, ">Yr 10"
    )
    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      Update it again please.

      Age mth to mth 2 =
VAR yeartoday =
    YEAR ( TODAY () )
VAR last1 =
    DATE ( yeartoday, 1, 1 )
VAR endyear =
    DATE ( yeartoday, 12, 31 )
VAR last2 =
    DATE ( yeartoday - 1, 1, 1 )
VAR last3 =
    DATE ( yeartoday - 2, 1, 1 )
VAR last4 =
    DATE ( yeartoday - 3, 1, 1 )
VAR last5 =
    DATE ( yeartoday - 4, 1, 1 )
VAR last6 =
    DATE ( yeartoday - 5, 1, 1 )
VAR last7 =
    DATE ( yeartoday - 6, 1, 1 )
VAR last8 =
    DATE ( yeartoday - 7, 1, 1 )
VAR last9 =
    DATE ( yeartoday - 8, 1, 1 )
VAR last10 =
    DATE ( yeartoday - 9, 1, 1 )
VAR last11 =
    DATE ( yeartoday - 10, 1, 1 )
RETURN
    SWITCH (
        TRUE (),
        'LDet'[Regdate] <= endyear
            && 'LDet'[Regdate] >= last1, "Yr 1",
        'LDet'[Regdate] < last1
            && 'LDet'[Regdate] >= last2, "Yr 2",
        'LDet'[Regdate] < last2
            && 'LDet'[Regdate] >= last3, "Yr 3",
        'LDet'[Regdate] < last3
            && 'LDet'[Regdate] >= last4, "Yr 4",
        'LDet'[Regdate] < last4
            && 'LDet'[Regdate] >= last5, "Yr 5",
        'LDet'[Regdate] < last5
            && 'LDet'[Regdate] >= last6, "Yr 6",
        'LDet'[Regdate] < last6
            && 'LDet'[Regdate] >= last7, "Yr 7",
        'LDet'[Regdate] < last7
            && 'LDet'[Regdate] >= last8, "Yr 8",
        'LDet'[Regdate] < last8
            && 'LDet'[Regdate] >= last9, "Yr 9",
        'LDet'[Regdate] < last9
            && 'LDet'[Regdate] >= last10, "Yr 10",
        'LDet'[Regdate] < last10
            && 'LDet'[Regdate] >= last11, ">Yr 10"
    )