Forum Discussion
Date Bins / Groups
Hi Andy1927 ,
You can use dax to create a calculated column, it is much simpler than use power query, you can try to use the following calculated column:
BINS = VAR A = DATEDIFF ( TODAY (), Table[End Date], MONTH ) RETURN IF ( ISBLANK ( Table[End Date] ), BLANK (), SWITCH ( TRUE (), A <= 2, "0-2mths", A >= 3 && A <= 5, "3-5mths", A >= 6 && A <= 11, "6-11mths", A >= 24, "24mth+" ) )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi Andy1927 ,
You can use dax to create a calculated column, it is much simpler than use power query, you can try to use the following calculated column:
BINS =
VAR A =
DATEDIFF ( TODAY (), Table[End Date], MONTH )
RETURN
IF (
ISBLANK ( Table[End Date] ),
BLANK (),
SWITCH (
TRUE (),
A <= 2, "0-2mths",
A >= 3
&& A <= 5, "3-5mths",
A >= 6
&& A <= 11, "6-11mths",
A >= 24, "24mth+"
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Thank you Dedmon, this is exactly what I needed. I've tweaked the code so <0 = Expired and I've added some extra bins.
I'm really grateful for this!
Best regards
Andy