Date Bins / Groups

[Andy1927]

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 between Todays date and an end date that exists in the data?

I'd like to do this using M language if possible

e.g. todays date is 14/06/2021 and the End Date in my data is 01/07/2021 = 0-2mths

                              14/06/2021 and the End Date in my data is 01/10/2021 = 3-5mths

                              14/06/2021 and the End Date in my data is 01/05/2022 = 6-11mths

and so on all the way to 24mths+

I do also have null values in this date column so I'll have inevitable errors that I'll need to address which I may to ask assistance about.

I'm quite new to Power BI by the way

Thanks,

Andy

[amitchandak]

@Andy1927 ,you have Date.IsInNextNMonths and Date.IsInNextNDays, that you can try to use , see if these can help

days - https://www.youtube.com/watch?v=sGwjqdXnV4g&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=111

https://www.youtube.com/watch?v=VuTRGM23wyQ&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=113

[Andy1927]

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

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]

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