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

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.