Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Unknowncharacte
Helper III
Helper III

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

CaseNumberDate OpenedDate ClosedCase Status
A12301/21/202302/01/2023Closed
A11102/23/2023 In Progress
A22203/05/2022 Review

 

My dax is as follows

 

Case Age Less Than 30 =

CALCULATE(
    COUNT('Fact Table'[Case Number]),
    'Fact Table'[Case Status] <> "Closed" && 'Fact Table'[Date Opened] <= TODAY() - 29
 
This does not work, and I was hoping someone can help me figure out why? It has to be a separate measure for each category so I can manipulate them as needed later

1 ACCEPTED SOLUTION

# 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)

View solution in original post

8 REPLIES 8
Ritaf1983
Super User
Super User

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")))))
Ritaf1983_0-1697215251741.png

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. 

Unknowncharacte_0-1697216804319.png

 

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

Sample Data.pbix

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

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

# 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  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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.