Create measure to calculate whether ClosedDate and CreatedDate are in same period?

[Anonymous]

Hi, hopefully someone can help with this.

I have a dataset that looks at tickets created and closed on a service desk. Both created date and closed date are joined to a calendar table.

What I want to be able to do is create a measure which counts number of tickets created AND closed in the selected period. For example, a user selects June 2018 and it will count all tickets that were created in june 2019 and solved in June 2019. Any without a solve date or a solve date higher than june 2019 won't show up, and neither will tickets created before or after this period. If the user then selects the whole of 2018 - it will apply this same logic to the entire year.

Does this make sense? Hopefully someone can help

Thanks!

[TeigeGao · Accepted solution]

HI @Anonymous ,

Assum that we have a table like below:

Then first we need to create a calendar table for slicer like below without relationship between above table:

Table = CALENDAR(DATE(2018,01,01),DATE(2019,12,31))

After that, we can create a measure using the following DAX query:

count_ticket =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
        ALL ( Table1 ),
        Table1[createddate] >= MIN ( 'Table'[Date] )
            && Table1[createddate] <= MAX ( 'Table'[Date] )
            && Table1[closeddate] >= MIN ( 'Table'[Date] )
            && Table1[closeddate] <= MAX ( 'Table'[Date] )
    )
)

The drag the measure to a card to display and drag the calendar to a slicer, the result will like below:

Best Regards,

Teige