Filter active records by two sets of date parameters

[Nadoedalo]

Hello,

I have a list of active records in excel that have a start date, and also closed records that also have a close date.

Each month one need to filter how many records were active at a certain month/year, which goes like this

- all records that have a start date earlier or equal to the current month/year, 

- and at the same time which have a close date later than the needed month/year

start date	closed date	record
01.01.2016	01.07.2022	abc12426
01.07.2017		xyz25142

Usually it's done manually by a person, but we'd like to know if we can put the data into Power Bi and see how it can be autocalculated for all time periods while new records are being added and the time go on.

Thanks a lot in advance.

[johnt75 · Accepted solution]

You could set up a date table not connected to the main data table and use the date table in report visuals. And you could create a measure like

Num Active Cases = 
var maxDate = MAX('Date'[Date])
return CALCULATE( COUNTROWS('Table'), 
'Table'[Start Date] <= maxDate && 
( ISBLANK('Table'[End Date]) || 'Table'[End Date] > maxDate) 
)

[Nadoedalo]

Oh My God, I am too dumb to figure out what it does, but it works! 🤔😥 No words enough to express my gratitude, thanks a million!