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:
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)
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.
Created a Relationship b/w Calendar Table and FreshWorksTickets based on Created Date.
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])
)
)
))
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.
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.
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.
That way, other users with the same issue can also benefit from your question.
Lastly, please take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
