Forum Discussion

ndna74's avatar
ndna74
Frequent Visitor
9 years ago

When to group dates into buckets

Hi

 

I have a table of loan data in Excel with each transaction having a date. I want to bucket these dates into "less than 3 months", "3-6 months" etc.

 

I can do this in Excel using IF/AND statements but would it be easier/better practice to do this in Power BI and if so how should I do this?

 

Many thanks

Nick

Need Help

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi ndna74,

     

    Please share some sample data to make the requirement more clear. It would be better if you can post the screenshots of sample data and the result of grouping dates into buckets you have achieved in Excel.

     

    Suppose that each transaction has a start date and end date, and you want to group these transactions depend on how long a transaction lasts.

     

    You can add two calculate columns:

    Diff = DATEDIFF('group dates'[StartDate],'group dates'[EndDate],MONTH)

Group =
IF (
    'group dates'[Diff] < 3,
    "less than 3 months",
    IF ( 'group dates'[Diff] < 6, "3-6 month", "more than 6 months" )
)


     

    Thanks,
    Yuliana Gu