cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BI_Analyticz
Helper V
Helper V

Open Tickets at any point in time

I am creating a report on How many Open Tickets, Closed Tickets and Open at the end of Year, Qtr, Month, Date (at any point in time using Date Hierarchy).

 

I was using the below codes for many projects and it was working fine but now it is not working in one of my data and I have no clue on whats wrong with data or the query or etc., 😂

 

I have Tickets table and DateDim (calendar) table with Active relationship between Datedim.Date and Tickets.CreationDate, Inactive relationship between Datedim.Date and Tickets.ResolutionDate

 

Opened = COUNTROWS(Tickets)  => Output is Correct
Closed = CALCULATE([Opened],USERELATIONSHIP(Tickets[ResolutionDate],DateDim[Date]))  => Output is Correct
 
Below Measure is show the wrong values.
 
Active =
VAR MaxDate = MAX(DateDim[Date])

VAR ActiveTickets =
CALCULATE(
COUNT(Tickets[ID]),
ALL(DateDim),
Tickets[CreationDate] <= MaxDate,
ISBLANK(Tickets[ResolutionDate])
|| Tickets[ResolutionDate] > MaxDate
)

RETURN
ActiveTickets
 
From Qtr 3 Jul the count of Active is showing wrong. (It should be 127 from Qyr End + 1291 Jul Opened - 30 Closed Jul = 1388. Similarly for all Years, etc., I have no clue.
 
( I have attached the PBIX and Data in Excel in this LINK ). Kindly help.
 
1 ACCEPTED SOLUTION
mahoneypat
Microsoft
Microsoft

The use of USERELATIONSHIP forces the relationship to the ResolutionDate column so your filter on CreationDate column isn't working as you'd expect.  To address it, the two number can be calculated separately in variables and then subtracted.

 

Active 2 =
VAR maxdate =
    MAX ( DateDim[Date] )
VAR openedtilnow =
    CALCULATE ( COUNT ( Tickets[ID] )ALL ( DateDim ), DateDim[Date] <= maxdate )
VAR closedtilnow =
    CALCULATE (
        COUNT ( Tickets[ID] ),
        ALL ( DateDim ),
        DateDim[Date] <= maxdate,
        NOT ( ISBLANK ( Tickets[ResolutionDate] ) ),
        USERELATIONSHIP ( DateDim[Date], Tickets[ResolutionDate] )
    )
RETURN
    openedtilnow - closedtilnow

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
mahoneypat
Microsoft
Microsoft

The use of USERELATIONSHIP forces the relationship to the ResolutionDate column so your filter on CreationDate column isn't working as you'd expect.  To address it, the two number can be calculated separately in variables and then subtracted.

 

Active 2 =
VAR maxdate =
    MAX ( DateDim[Date] )
VAR openedtilnow =
    CALCULATE ( COUNT ( Tickets[ID] )ALL ( DateDim ), DateDim[Date] <= maxdate )
VAR closedtilnow =
    CALCULATE (
        COUNT ( Tickets[ID] ),
        ALL ( DateDim ),
        DateDim[Date] <= maxdate,
        NOT ( ISBLANK ( Tickets[ResolutionDate] ) ),
        USERELATIONSHIP ( DateDim[Date], Tickets[ResolutionDate] )
    )
RETURN
    openedtilnow - closedtilnow

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Brilliant to the core. You are awesome...! Thank you so much @mahoneypat 

BI_Analyticz
Helper V
Helper V

Hi,

My proposed solution will not work here because i want to create one row for date between the 2 dates (Creation date and Resolution date).  When i do that the number of rows balloon to 10 milltion rows.  Someone else will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you @Ashish_Mathur for trying.

BI_Analyticz
Helper V
Helper V

Kindly somebody help...

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.