Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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,   I want to create a report and graps for a selected time period showing...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

Doh,

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    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 :