Forum Discussion
Solved
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 CaseN...
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)
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
Here you go, not all categories will have data for them, data changes daily, and case counts for each category vary as well
Another attempt at the measure:
Case Age Less Than 30 =CALCULATE(COUNT('Fact Table'[Case]),'Fact Table'[Case Status] <> "Closed"&&'Fact Table'[Date Opened] > TODAY() - 29&&'Fact Table'[Date Opened] <= TODAY())
Seems to work but I am not sure how to adjust it for 30 - 60 daysI think I got it:
Case Age 30 to 60 =CALCULATE(COUNT('Fact Table'[Case]),'Fact Table'[Case Status] <> "Closed"&&'Fact Table'[Date Opened] > TODAY() - 60&&'Fact Table'[Date Opened] <= TODAY() - 30)
If there is a better way to do this, I welcome the feedback