Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Categorizing Consecutive Months With Zero Values Into Buckets

 

I've constructed a data model around utilization for my company's fleet within Power BI desktop. We have a number of different columns in the data model. Specifically, mileage, VIN, start date, and end date (see below for example table).

 

 

What I'm trying to accomplish is if mileage is equal to 0 for one month it is categorized into a bucket labeled 0-30 days, if mileage is equal to 0 for two consecutive months it is categorized as 31-60 days, and 0 mileage for more than 3 consecutive months would be categorized as >60 days. From the example above, this vehicle would be categorized in the ">60 days" bucket. Is there an easy way to do this within the data model using DAX? Please let me know if you have any follow up questions. Thank you!