Count of days open tickets have been open for - on given date

[AshwinC]

Hi,

I am trying to build a time series trend report on open tickets and the days it was open at the end of each month.

I have already have measure which can calculate the total open tickets at any given date. But the next one is tricky

I need to calculate the count of days the ticket was open on a given date. 

Then i need to group the Days it was open into 0-10, 10-30, 30+.

I need to build a graph to show the trend of how the open ticktets in each group vary over the month.

Regards,

Ashwin

[AshwinC]

Hi @Shishir22 

The sample data of the record id and Open Date and Close date. Some tickets are still open.

Record Id	Open Date	Resolved date
1	1/01/2022	15/01/2022
2	31/01/2022	4/03/2022
3	2/03/2022	3/05/2022
4	1/04/2022	15/04/2022
5	1/05/2022
6	31/05/2022
7	30/06/2022	6/08/2022
8	30/07/2022
9	29/08/2022	6/09/2022
10	28/09/2022
11	28/10/2022	3/11/2022
12	27/11/2022
13	27/12/2022

What i am trying to achive here is calculate how many tickets are open at the end of each month and how many days are they are open (Date difference between Open date and End of month Date) and group the days open into categories. The output i am expecting is something as below

Open Tickets	Jan	Feb	Mar
0-10	1	2	4
10-30	2	1	2
30+	0	1	1

Hopefully this explains.

Regards,

Ashwin

[Shishir22]

Hello @AshwinC -  

By this- Date difference between Open date and End of month Date

Do you mean End of current month date?

Thanks,

Shishir

[AshwinC]

Hi @Shishir22 

End of the selected month date.

For example if a ticket is opened on Jan 1, 2022 and closed on March 15.

For Jan the days will Jan 1, 2022 and 31 jan 2022 = 30

For feb the days will be Jan 1, 2022 and 28 Feb 2022 = 58

We do not count this at end of march as this has been resolved.

Thanks

[v-chenwuz-msft]

Hi @AshwinC ,

Maybe you can try this expression to do this.

Measure =
VAR _1 =
    SUMMARIZE (
        'Table',
        'Table'[Record Id],
        'Table'[Open date],
        'Table'[Resolved date],
        "day",
            IF (
                [Open date] > MAX ( 'calendar'[Date] )
                    || [Resolved date] < MIN ( 'calendar'[Date] ),
                -1,
                IF (
                    ISBLANK ( [Resolved date] )
                        || MAX ( 'calendar'[Date] ) < [Resolved date],
                    MAX ( 'calendar'[Date] ) - [Open date],
                    [Resolved date] - [Open date]
                )
            )
    )
RETURN
    COUNTROWS (
        FILTER (
            _1,
            [day] >= SELECTEDVALUE ( 'Rows'[Min] )
                && [day] <= SELECTEDVALUE ( 'Rows'[Max] )
        )
    )

Result:

More details please refer the pbix in the end.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AshwinC]

Thanks for the reply.

Can you please share the pbix?

Ashwin

[v-chenwuz-msft]

Hi @AshwinC ,

I forgot to upload the pbix file.

Pbix file in the attachment.

Best Regards

Community Support Team _ chenwu zhu

[Shishir22]

Hello @AshwinC ,

Not very clear. Can you please share sample data and expected result in tabular format?

Thanks,

Shishir