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.

v-kkf-msft's avatar
v-kkf-msft
Community Support
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

    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?

  • 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 :