Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
Anonymous
Not applicable

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

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...

 

 

TICKETCREATEDRESOLVED
SD-0114 Dec 2021 
SD-0215 Dec 202118 Dec 2021
SD-0316 Dec 202105 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 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

5 REPLIES 5
PaulOlding
Solution Sage
Solution Sage

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

Anonymous
Not applicable

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
Frequent Visitor

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.


Anonymous
Not applicable

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. 🙂

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.