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)
I 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
If there is a better way to do this, I welcome the feedback
Unknowncharacte sorry , i am not near my computer , so didnt see apbix that you attached.
The logic of you formula seems very similar with my , so i think that it is good enough 😊
Mark the helpful responses as as solutions to help others.