Forum Discussion

Unknowncharacte's avatar
Unknowncharacte
Helper III
2 years ago
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...
Need Help
Tutorial Requests
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    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

  • Unknowncharacte's avatar
    Unknowncharacte
    2 years ago
    # 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)
Unknowncharacte's avatar
Unknowncharacte
Helper III
2 years ago

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 days
Unknowncharacte's avatar
Unknowncharacte
Helper III
2 years ago

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
  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    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.

  • Unknowncharacte's avatar
    Unknowncharacte
    Helper III
    2 years ago
    # 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)