Forum Discussion

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 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.

Need Help

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.

Filtering open tickets based on date.pbix64 KB

7 Replies

PhilipTreacy
Super User
4 years ago
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
  Not applicable
  4 years ago
  How do I add an attachment here.
  
  (I am testing the measures provided by v-kkf-msft now)
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.
Filtering open tickets based on date.pbix64 KB
- 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
  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
    Not applicable
    4 years ago
    Doh,
    Having just reread mthat i realize its not right, sorry.

Forum Discussion

Filtering open tickets based on date

7 Replies

Related content

Submit ticket

dynamic x-axis based on filter

Dynamic Filtering based on date selection

Calculate Based on Filtered Segment

country based calendar filter