cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
russgove2
Frequent Visitor

Filtering open tickets based on date

Hi,

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

russgove2_0-1632520432838.png

 

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 :
russgove2_1-1632521049995.png

 

 

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.

 

 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @russgove2 ,

 

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

 

vkkfmsft_0-1632733770578.png

 

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.

View solution in original post

7 REPLIES 7
v-kkf-msft
Community Support
Community Support

Hi @russgove2 ,

 

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

 

vkkfmsft_0-1632733770578.png

 

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.

View solution in original post

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

Doh,

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

 

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 :
russgove2_0-1632863114341.png

 

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
Super User
Super User

HI @russgove2 

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

Thanks

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


How do I add an attachment here.

 

(I am testing the measures provided by  now)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!