Forum Discussion

Andy1927's avatar
Andy1927
Helper 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 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

 

 

 

 

 

 

 

 

 

 

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

4 Replies

    • Andy1927's avatar
      Andy1927
      Helper 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
        Community 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