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

    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. 

     

    • Ritaf1983's avatar
      Ritaf1983
      Super User
      2 years ago

      Hi Unknowncharacte 
      Please share a link to pbix with your sample data

      • Unknowncharacte's avatar
        Unknowncharacte
        Helper III
        2 years ago

        Here you go, not all categories will have data for them, data changes daily, and case counts for each category vary as well

        Sample Data.pbix