Dynamic Function To Count Values in a Date Range

[mmcquil2]

I have a table that has an open and close date for Work Orders. I want to show, month by month, how many Work Orders were/are open. It is easy to just track a history going forward and query the data but I would like to show the history up to this point and not have to query so many files going forward.

Any ideas?

Thanks,

Michael

[v-yangliu-msft · Accepted solution]

Hi  @mmcquil2 ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Date = CALENDAR(MIN('Table'[start date]),MAX('Table'[end date]))

2. Create measure.

Count_open =
var _select=SELECTEDVALUE('Date'[Date])
var _maxdate=EOMONTH(_select,0)
var _mindate=DATE(YEAR(_select),MONTH(_select),1)
return
COUNTX(FILTER(ALL('Table'),'Table'[end date]>=_mindate&& 'Table'[end date]<=_maxdate&&'Table'[end date]<>BLANK()),[Orders])

Flag =
var _select=SELECTEDVALUE('Date'[Date])
var _maxdate=EOMONTH(_select,0)
var _mindate=DATE(YEAR(_select),MONTH(_select),1)
return
IF(
    MAX('Table'[end date])>=_mindate&& MAX('Table'[end date])<=_maxdate&&MAX('Table'[end date])<>BLANK(),1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

When selecting 2022.3.20 in the calendar table, the count of [End date] in this March 2022.3.1 and 2022.3.31 time interval will be displayed in Viusal. If it is empty, it will not be counted.

Please click here for the pbix file.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mh2587]

CALCULATE (
COUNTROWS ( TableName ),
FILTER (
TableName,
TableName[Open Date] <= MAX ( 'Calendar'[Date] )
&& TableName[Close Date] >= MIN ( 'Calendar'[Date] )
)
)

//I am assuming you have calendar table

[mmcquil2]

So I figured out the date table. The issue I am having now is that it does not count any rows if there is not a close date. Obviously if there is no close date, then it should be considered open. I would assume this is a simple "if" statement but it is not currently coming to me.

Thanks,

Michael

[mmcquil2]

Thanks for the response! When you say calendar table, are you asking if I have a seperate table built out that lists MonthYear? As in:

January 2021

February 2021

March 2021

etc.

[mh2587]

Yes use max of open date and min of open date then create the separate date table 

Then related this table with your other or fact table

[mmcquil2]

I believe this works, thanks! I am still having the issue of it not counting rows that do not have close dates. How can I get this formula to count rows that have an open date but no close?

Thanks,

Michael