# Remaining Open tickets as of Date selection cumulative with Open on and closed on that date

Hello All, I am an above average beginner in Dax but been creating Power BI solutions since the first version, very good at pretty and most of the basic dax I know till this one, it is what inspired me to join your site. . But just recent was challenged with this by my boss. We have a ticket system and has open and resolved, so if I want to know how many tickets are open as of this date the formula for “this day” =If createdDate <=Date and ResolveDate is null then TRUE, or If CreatedDate <=Date and ResolvedDate >Date then TRUE
Date being todays date. How I did this in SQL was a job that capture each day. BUT I want Power BI Dax to do this. Where on each day it does that math to say what was remaining open that day.

so no matter what date I select in the range it will always show on 07/13 that day there was 734 remaining ticket open, 170 were open on that day and the number that were closed on 07/13

I created this calendar date
Calendar = CALENDAR(MIN(FreshWorksTickets[CreateDate]),TODAY())

Here is the one formula that I used and it just wont do it.
Active Count sue = CALCULATE(
DISTINCTCOUNT(FreshWorksTickets[ID]),
FILTER (FreshWorksTickets,
(
FreshWorksTickets[CreateDate]<= MIN (‘Calendar’[Date]) &&
(
FreshWorksTickets[Resolved Date] = BLANK() ||
FreshWorksTickets[Resolved Date] >= MAX(‘Calendar’[Date])
)
)
),
VALUES(FreshWorksTickets[ID])
)

and I tried this one:
isopen =
IF (SELECTEDVALUE ( FreshWorksTickets[CreateDate] ) <= SELECTEDVALUE ( ‘CALENDAR’[Date] ) && SELECTEDVALUE(FreshWorksTickets[CntOpen])=1,1,IF (SELECTEDVALUE ( FreshWorksTickets[CreateDate] ) <= SELECTEDVALUE ( ‘CALENDAR’[Date] ) && SELECTEDVALUE(FreshWorksTickets[ResolvedDate])>=SELECTEDVALUE(‘Calendar’[Date].[Date]),1,0))

counts = SUMX(FreshWorksTickets,[isopen])

First time Sam opening a forum so I hope I did it right ,especially uploading the pic.
The data in the pic I entered in excel and upload for the example.

clip of live data

I can get this much but not the total previous open plus what is still open

Hi @suellenjackson, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

• A mockup of the results you want to achieve
• Your current work-in-progress PBIX file

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

Ok, sorry, I work at a hospital so I needed to make sure I don’t have data in there that should not be, cleaned up. and attaching.
FreshDeskTickets for Dax.pbix (8.6 MB)

1 Like

Thank you for sending your PBIX. Bumping this post for more visibility.

1 Like

I have looked into the requirement but couldn’t really figure out how are you getting the values as shown in the Table.

I have tried to apply logic provided and getting different values from the ones provided. Is there any other filter that needs to be applied. Changes Explained below.

1. Created a Relationship b/w Calendar Table and FreshWorksTickets based on Created Date.
2. Created below DAX expression. This will give the count of Tickets with CreatedDate <= Calendar Date in Table and Resolved Date (Blank or ResolvedDate >Date) but it doesn’t matches with “OpenAsofDate” values.

Active Count sue Ankit = CALCULATE(
DISTINCTCOUNT(FreshWorksTickets[ID]),all(‘Calendar’),
FILTER (all(FreshWorksTickets[CreateDate],FreshWorksTickets[ResolvedDate]),
(
FreshWorksTickets[CreateDate]<= MIN (‘Calendar’[Date]) &&
(
FreshWorksTickets[ResolvedDate] = BLANK() ||
FreshWorksTickets[ResolvedDate] >= MAX(‘Calendar’[Date])
)
)
))

1. Not sure what are you trying to achieve with “isopen” expression. If you need Tickets open on Calendar Date and Resolution Date (Blank or > Calendar Date"), try below formula. This matches with Opened in the “AsOfOpen” table.

IsOpen Ankit = CALCULATE(
DISTINCTCOUNT(FreshWorksTickets[ID]),
FILTER (FreshWorksTickets,
(
FreshWorksTickets[CreateDate]<= MIN (‘Calendar’[Date]) &&
(
FreshWorksTickets[ResolvedDate] = BLANK() ||
FreshWorksTickets[ResolvedDate] >= MAX(‘Calendar’[Date])
)
)
))

2. Resolved - Don’t know what’s the requirement here.

Check attached PBIX file with page "Duplicate of This is the one I want " and measures given above and see if they are helpful . If requirement is different, then please Re-explain the logic.

FreshDeskTickets for Dax.pbix (8.6 MB)

Thanks
Ankit J

YOU DID IT, thank you thank you. @ankit. Amazing. I will study closer to see what I was missing, but your Formula Active Count sue Ankit does exactly what I needed.

1 Like

And it worked… @ankit solved it, Thank you…

sj

Hello @suellenjackson , glad to know @ankit helped find a solution