March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
Hi @Anonymous
@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
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
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.
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
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. 🙂
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
36 | |
27 | |
26 | |
20 | |
15 |