cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

combining data into categories

Hi All,

 

Is there a way of being able to categorise my data into certain months for example, I have data that I want to be able to categorise into 0-12 months and 25-48 months and anything else would be "Other", what would be the quickest way of doing this?

 

Current Date30/09/2019
  
DateCategory
01/12/2002Other
01/09/20190-24
22/05/201825-48
13/07/2005Other
01/12/20180-24
01/01/20190-24
01/04/20190-24
05/05/201825-48
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: combining data into categories

@UK_User123456  Please add a new column as below

 

Category = 
VAR _MonthsDiff = DATEDIFF([Date],TODAY(),MONTH)
RETURN IF(_MonthsDiff<=24,"0 -24",IF(_MonthsDiff<=48,"25 - 48","Other"))

Added MonthsDiff column, just for reference.

 

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

Re: combining data into categories

@UK_User123456  Please add a new column as below

 

Category = 
VAR _MonthsDiff = DATEDIFF([Date],TODAY(),MONTH)
RETURN IF(_MonthsDiff<=24,"0 -24",IF(_MonthsDiff<=48,"25 - 48","Other"))

Added MonthsDiff column, just for reference.

 

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

Community Support Team
Community Support Team

Re: combining data into categories

Hi @UK_User123456 ,

 

You can also use M query to do it. Here is my test file, you could download and refer to it.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Re: combining data into categories

Hi,

 

I cannot see how you have created the workings as it wont let me update power bi? Are you able to post the working?

 

Thank you.

Community Support Team
Community Support Team

Re: combining data into categories

Hi @UK_User123456 ,

 

You could open query editor and find "APPLIED STEPS" in the right part. It shows each step to get the final result.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors