Forum Discussion
Calculating Time Categories
Hi,
I need to bucket # of days a case has been open as of today
My categories are
Less Than 30 Days
30 to 60 Days
61 to 90 Days
91 to 120
More than 120
Sample of my data
| CaseNumber | Date Opened | Date Closed | Case Status |
| A123 | 01/21/2023 | 02/01/2023 | Closed |
| A111 | 02/23/2023 | In Progress | |
| A222 | 03/05/2022 | Review |
My dax is as follows
Case Age Less Than 30 =
Hi Unknowncharacte
You can create 2 measures :
1.count from today = DATEDIFF(max('Table'[Date Opened]),TODAY(),DAY)2.
dates bins = if (max('Table'[Case Status])="closed", blank(),if ([count from today]>91, "91-120",if([count from today]>61,"61-90",if([count from today]>31,"31-60",if([count from today]<=30,"0-30")))))If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
- # 30 to 60 Days =
CALCULATE(COUNT(FactTable[Case]),DATEDIFF(FactTable[Date Opened],TODAY(),DAY)>=30&&DATEDIFF(FactTable[Date Opened],TODAY(),DAY)<=60)# Less than 30 days =
CALCULATE(COUNT(FactTable[Case]),DATEDIFF(FactTable[Date Opened],today(),DAY)<30)
8 Replies
Hi Unknowncharacte
You can create 2 measures :
1.count from today = DATEDIFF(max('Table'[Date Opened]),TODAY(),DAY)2.
dates bins = if (max('Table'[Case Status])="closed", blank(),if ([count from today]>91, "91-120",if([count from today]>61,"61-90",if([count from today]>31,"31-60",if([count from today]<=30,"0-30")))))If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Would you be able to adjust so it shows counts like this? I am using this with another column - case owner, hence the names, I just wanted to show the final result I am after.
Hi Unknowncharacte
Please share a link to pbix with your sample data