Forum Discussion
Dynamic Function To Count Values in a Date Range
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
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
6 Replies
CALCULATE (
COUNTROWS ( TableName ),
FILTER (
TableName,
TableName[Open Date] <= MAX ( 'Calendar'[Date] )
&& TableName[Close Date] >= MIN ( 'Calendar'[Date] )
)
)//I am assuming you have calendar table
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.
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
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
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
