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...
|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.
Solved! Go to Solution.
@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 =
Tickets[CREATED] <= _ReportingPeriodEnd
VAR _WasOpen =
Tickets[CREATED] <= _ReportingPeriodEnd,
Tickets[RESOLVED]) >= _ReportingPeriodEnd
VAR _Result = _Blank + _WasOpen
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
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...
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.
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. 🙂
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.