Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

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



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.





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.



View solution in original post

Solution Sage
Solution Sage

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 =



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



Frequent Visitor


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.



Frequent Visitor

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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors