Forum Discussion

Andy1927's avatar
Andy1927
Icon for Helper I rankHelper I
5 years ago
Solved

Date Bins / Groups

Hi,   I'm having to rebuild my PowerBI report because some column names in the live data source have changed. Is it possible to create a conditional column in Power Query where I can group the time...
Need Help
Show and Tell
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

Andy1927's avatar
Andy1927
Icon for Helper I rankHelper I
5 years ago

thanks for posting these links!

I'm still unsure if this is the best way to achieve creating Bins for my dates - would DAX be a better option?

 

My problem is that a date that belongs to 0-2 months this month will belong to 3-6 month bin next month.

I would like anything before todays date to be 'Expired' and anything that is more than 2 years (24months) to show 24mths+

 

Is M language the best option for this or should I use DAX? Either way I'm still a bit confused about how to do this.

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

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

  • Andy1927's avatar
    Andy1927
    Icon for Helper I rankHelper I
    5 years ago

    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