Display Data Based on Multiple Date-bound Filters on a Chart

[Anonymous]

Hi everyone,

I've spent a long time mulling over this problem and was hoping you'd be able to help me out. I've tried searching for a solution to this on these forums with no success and have asked around at work for help but no one's been able to assist me!

Background

I've generalised the problem below as much as I can to hopefully simplify it. I manage a number of reports which can either be 'open' or 'closed'. The database only stores the current status of a report and it isn't possible to directly lookup a status in the past. I can, however, calculate a historical report status based on other data fields.  

What I'm trying to achieve

I want to be able to display the number of 'open' reports for any time period ideally with the ability to drill down within a date hierarchy. Here's what it currently looks like in a manually manipulated Excel spreadsheet I've inherited:

Note that the Excel screengrab has datapoints at irregular points in time because that's when the data was manually processed. 

Data fields available

DATE_REPORTED (date the report was created and when it becomes 'open')

DATE_CLOSED (date the report was 'closed')

Method to determine historical report status

Using the example of 31 March 2022, we can see what reports are 'open' by only counting/showing reports that were DATE_REPORTED on or before 31 March 2022 AND were DATE_CLOSED after 31 March 2022.

Here's a really simple output of what this would look like in PowerBI:

The problem then is how to iterate this process for each element within a date hierarchy as it'd be helpful to examine this by year, quarter, month and day.  Iterating manually would be extremely inefficient as it's necessary to evaluate each day, e.g by looking at 2021 as a whole:

DATE_REPORTED <= 31 December 2021 AND DATE_CLOSED > 31 December 2021 neglects any reports that were 'closed' during 2021 and so some reports will be erroneously shown as 'open'.

Some of my thoughts:

I strongly suspect that it'd be difficult to examine this within a date hierarchy as a report can be 'open' for many months before it's 'closed'.  So the sum of daily 'open' reports would not equal the number of 'open' reports in a week, month, year etc... I could get around this using Count (Distinct) Values with the REPORT_ID data field for each date within a time period but I'm unsure of the implementation given that I have to solve how to display the number of 'open' reports first.

At this point I feel that it's necessary for the number of 'open' reports to be calculated for each day in the dataset. That could then be presented as an average for different date bands, as absolute values at specific dates at a set frequency - e.g. last day of the month, or as a count of distinct values based on the time period examined.

With the above in mind it's probably most appropriate to only display the number of 'open' reports at fixed points in time with the date heirarchy set to display the number of 'open' reports based on the last day of each Day, Month, Quarter and Year.

It'd still be useful to see the distinct count for a time period as it'd then be possible to articulate which reports were open for a whole month, year etc...

Ultimately I'm not sure how to solve this problem and I'd love to hear your thoughts! If you need any more information or need me to explain anything in greater detail or just in a different way let me know. 

Thanks a lot.

[Anonymous]

Thanks a lot for the help with this, I've fully implemented the solution now. The key was definitely in using a linked table.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You could first refer to the solution I provided for a similar question recently https://community.powerbi.com/t5/Desktop/Running-totals-between-to-dates/td-p/2530660 

So if it is on the date hierarchy, it will work correctly. You can use COUNTX to replace SUMX in the measure and change corresponding column name. 

However when it comes to the month/quarter/year hierarchy, it will be difficult. Imagine that one report was created on 2nd Dec 2021 and closed on 25th Dec 2021. It was created and closed in the same calendar month. So how do you want to define its status for this month and for the quarter&year it is in? 

The difficulty is at the logic not at the calculation. One practice is to count the open reports on the last date of every period (month, quarter, year) and use it as the monthly/quarterly/yearly data. In that way, the measure for date hierarchy will work too as MAX('Date'[Date]) will always get the last date in its corresponding period and compare that with the created date and closed date for every report. 

If you want to calculate the average count of open reports for other hierarchies, you need to generate a new table to have daily counts of open reports. You can try the following method to create this table.

First create a table with all dates you need to count. You can expand the date range per your need. 

New Table = CALENDAR(MIN(ReportStatus[Created Date]),MAX(ReportStatus[Closed Date]))

Then add a new column to above new table. This column returns the count of open reports for every date. 

Open reports count = COUNTROWS(FILTER(ReportTable, ReportTable[Created Date] <= 'New Table'[Date] && ReportTable[Closed Date] > 'New Table'[Date])) + 0

You can add Month/Quarter/Year columns to this new table and use "Open reports count" column to calculate average counts for periods. 

Hope this helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.