Include rows where the date value is greater than the period end being reported

[DStearn]

Hi, I am new to PowerBI and have been busy putting together a report to replace our current excel dashboard that reports Service Tickets.

My issue is that when we are ready to report, some of the tickets that were open at the reporting period end date have since closed, therefore I am not getting a true reflection of tickets that were open. For example...

TICKET	CREATED	RESOLVED
SD-01	14 Dec 2021
SD-02	15 Dec 2021	18 Dec 2021
SD-03	16 Dec 2021	05 January 2022

Period Reporting End Date - 31 December 2021

Report run on - 15 January 2022

In the above example, as at 31/12/21, there are two tickets open that have not yet been resolved (SD-01 and SD-03). However if I create a measure to countblank on Resolved column, the answer returned is 1.

I am struggling to create a measure that counts all records where a ticket was open as at the reporting period end date of 31/12/21 and include those that have a resolved date after 31/12/2021.

Any help would be greatly appreciated.

Thanks

Dean 

[PaulOlding]

Hi @DStearn 

@I'm going to suggest using the CALCULATE function to change the 'filter context' (ie the filters that apply to the calculation).

Before getting to that though - how do you know the reporting period?  A common way to do it is having a date table that's not connected to any other tables in the model and allowing the user to select a date.  This measure uses this method.

Open Tickets =
VAR _ReportingPeriodEnd = MAX('Date'[Date])

VAR _Blank =
CALCULATE(

     COUNTROWS(Tickets),

     ISBLANK(Tickets[RESOLVED]),

     Tickets[CREATED] <= _ReportingPeriodEnd

)

VAR _WasOpen =

CALCULATE(

     COUNTROWS(Tickets),

     Tickets[CREATED] <= _ReportingPeriodEnd,

     Tickets[RESOLVED]) >= _ReportingPeriodEnd

)

VAR _Result = _Blank + _WasOpen

RETURN

    _Result

If you don't want to use the date table to have the user select the reporting period end date you can alter the VAR _ReportingPeriodEnd line. With the date table you can do things like put it as the x-axis on a bar or line chart to show the change in number of open tickets over time

[DStearn]

Many thanks for the response @PaulOlding.

The report is only run by myself and I am manipulating the datasource in the backgorund. So, records that were created after the reporting period end, I am deleting from the file. As mentioned, I am new to all this, so in comparison to the reports an experienced PowerBI person creates, mine will seem very clunky and inefficient, with that being said, what I am so far producing is 100 times better than what we had, so am happy....

As I become more experienced, I aim to replace with something better....

My report does contain a Date table, so thank you for the useful tip...

thanks

Dean 

[joris]

Try

calculate(countrows([table]), filter(values(table[resolved]),OR(table[resolved] <= EOMONTH(today(),-1),isblank(table[resolved]))))

This will only count the tickets where resolved = blank or that have been resolved on or before the last day of the previousmonth.

[DStearn · Accepted solution]

Hi @joris 

That seems to have done the trick. At first, my results were not as expected, however, I change the <= to > and now getting the correct results.

Thanks you for this.

Dean

[joris]

Glad to hear it worked. The results in my example were for resolved cases, but after reading your post again you were looking for unresolved ones. It can be used for both, by changing the operator, as you figured out already. 🙂