Filtering open tickets based on date

[Anonymous]

Hi,

I have a table called testdata  that contains support tickets with an Id , Opened Date and Closed Date like this,

I want to create a report and graps for a selected time period showing the number of open tickets that existed before the first date time period, the number of tickets opened during the time period, the number of tickets closed during the time period, and the neumber of tickets that were left opened after the time period ended.

I got it working for just a single-day  time period using a date table with these calcukated columns added :

TicketsIn = CALCULATE(

COUNTROWS(TestData),

FILTER(TestData,

(TestData[Opened_Date]<TestDates[Date] && ISBLANK(TestData[Closed_Date])

||

(TestData[Opened_Date]<TestDates[Date] && TestData[Closed_Date]>=TestDates[Date])

)))

TicketsOut = CALCULATE(

COUNTROWS(TestData),

FILTER(TestData,

(TestData[Opened_Date]<=TestDates[Date] && ISBLANK(TestData[Closed_Date])

||

(TestData[Opened_Date]<=TestDates[Date] && TestData[Closed_Date]>TestDates[Date])

)))

OpenedTickets = CALCULATE(

COUNTROWS(TestData),

FILTER(TestData,

(TestData[Opened_Date]=TestDates[Date]

)))

ClosedTickets = CALCULATE(

COUNTROWS(TestData),

FILTER(TestData,

(TestData[Closed_Date]=TestDates[Date]

)))

CurrentTickets = CALCULATE(

COUNTROWS(TestData),

FILTER(TestData,

(TestData[Opened_Date]<=TestDates[Date] && ISBLANK(TestData[Closed_Date])

||

(TestData[Opened_Date]<=TestDates[Date] && TestData[Closed_Date]>=TestDates[Date])

)))

These all work fine when the granularity of the report is a single day :

but now i want these to be applied to a given month, quarter year .../

I understand that i need to use measures rather than calculated columns for this. So I added a new measure to mey dates datble :

MTicketsIn = CALCULATE(

COUNTROWS(TestData),

FILTER(TestData,

(TestData[Opened_Date]<MIN(TestDates[Date].[Date]) && ISBLANK(TestData[Closed_Date])

||

(TestData[Opened_Date]<MIN(TestDates[Date].[Date]) && TestData[Closed_Date]>=MAX(TestDates[Date].[Date])

))))

i thought using the MIN(TestDates[Date].[Date])  and MAX(TestDates[Date].[Date]) functions  within the same calculation i used for single dates would work but it just shows blanks. 

I tried with FIRSTDATE and LASTDATE as well, bit it too shows blanks.

Anyone got advice on how to do this.

Links to Youtube  vidos or articles on this topic would be appreciated.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please try the following measures:

MTicketsIn = 
CALCULATE (
    COUNTROWS ( TestData ),
    FILTER (
        TestData,
        (
            TestData[Opened_Date] < MIN ( TestDates[Date] )
                && ISBLANK ( TestData[Closed_Date] )
                || (
                    TestData[Opened_Date] < MIN ( TestDates[Date] )
                        && TestData[Closed_Date] >= MIN ( TestDates[Date] )
                )
        )
    )
)

MTicketsOut = 
CALCULATE (
    COUNTROWS ( TestData ),
    FILTER (
        TestData,
        (
            TestData[Opened_Date] <= MAX ( TestDates[Date] )
                && ISBLANK ( TestData[Closed_Date] )
                || (
                    TestData[Opened_Date] <= MAX ( TestDates[Date] )
                        && TestData[Closed_Date] > MAX ( TestDates[Date] )
                )
        )
    )
)

MCurrentTickets = 
CALCULATE (
    COUNTROWS ( TestData ),
    FILTER (
        TestData,
        (
            TestData[Opened_Date] <= MIN ( TestDates[Date] )
                && ISBLANK ( TestData[Closed_Date] )
                || (
                    TestData[Opened_Date] <= MIN ( TestDates[Date] )
                        && TestData[Closed_Date] >= MAX ( TestDates[Date] )
                )
        )
    )
)

MClosedTickets = 
CALCULATE (
    COUNTROWS ( TestData ),
    FILTER (
        TestData,
        (
            TestData[Closed_Date] >= MIN ( TestDates[Date] )
                && TestData[Closed_Date] <= MAX ( TestDates[Date] )
        )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Again!

Just looking at this again the currentTickets looks fine on the day view but on the month view it shows only one ticket was active during the month. I would have thought it would show that it would show All 7 as active for september because all the tickets were opened in that month so (incase anyone looks back at this) i modified the MCurrentTickets to be 

MCurrentTickets =

CALCULATE (

COUNTROWS ( TestData ),

FILTER (

TestData,

(

//If it was open before this period and never closed

TestData[Opened_Date] <= MIN ( TestDates[Date] )

&& ISBLANK ( TestData[Closed_Date] )

||

//If it was opened or closed in this period

(TestData[Opened_Date] <= MIN ( TestDates[Date] )

||

TestData[Opened_Date] <= MAX( TestDates[Date] )

)

)

))

So your tickets in plus opened tickets in a period - current tickets (any ticket active in the period) minus Closed tickets = =tickets out.

aka.

BegininningBalance+additions=current/active 

and current -closed =endingbalance

[Anonymous]

Doh,

Having just reread mthat i realize its not right, sorry.

[Anonymous]

MCurrentTickets =

CALCULATE (

COUNTROWS ( TestData ),

FILTER (

TestData,

(

//If it was open before this period and never closed

TestData[Opened_Date] <= MIN ( TestDates[Date] )

&& ISBLANK ( TestData[Closed_Date] )

||

//If it was opened in this period

(TestData[Opened_Date] >= MIN ( TestDates[Date] ) && TestData[Opened_Date] <= MAX ( TestDates[Date] ) )

||

//If it was closed in this period

(TestData[Closed_Date] >= MIN ( TestDates[Date] ) && TestData[Closed_Date] <= MAX ( TestDates[Date] ) )

)

)

)

works :

[Anonymous]

They all work Great, Thank you so much!

So my sample MticketsIn above was pretty close . I was using TestDates[Date].[Date} instead of just TestDates[Date]

What does TestDates[Date].[Date] represent?

[PhilipTreacy]

HI @Anonymous 

Please supply your PBIX file so I don't have to recreate all of your data and measures by hand.

Thanks

Phil

[Anonymous]

How do I add an attachment here.

(I am testing the measures provided by v-kkf-msft now)