Forum Discussion
Counting objects/month based on Start and End Date
Hi,
I have a list of work orders (WO) and for each of them their start date, end date and $value.
We would like to count the number of WOs which are active for every month. Some WOs start and finish within the month, but some spread over several months - and these are the ones causing the problem, we do not physically have a table listing the WO for each month.
Below is an example of the dataset (let's call it Transactions):
| WO_No | StartDate | EndDate | $_Value |
| WO01 | 14/08/2021 | 27/08/2021 | 100 |
| WO02 | 14/08/2021 | 24/08/2021 | 100 |
| WO03 | 15/08/2021 | 12/12/2021 | 100 |
| WO04 | 15/08/2021 | 20/12/2021 | 100 |
| WO05 | 3/09/2021 | 7/09/2021 | 100 |
| WO06 | 5/09/2021 | 14/09/2021 | 100 |
| WO07 | 12/09/2021 | 18/09/2021 | 100 |
| WO08 | 20/09/2021 | 15/11/2021 | 100 |
| WO09 | 3/10/2021 | 15/10/2021 | 100 |
| WO10 | 5/10/2021 | 12/11/2021 | 100 |
| WO11 | 6/10/2021 | 6/12/2021 | 100 |
| WO12 | 1/11/2021 | 5/11/2021 | 100 |
| WO13 | 2/11/2021 | 15/11/2021 | 100 |
| WO14 | 3/11/2021 | 5/12/2021 | 100 |
| WO15 | 2/12/2021 | 15/12/2021 | 100 |
| WO16 | 5/12/2021 | 5/01/2022 | 100 |
As an example, WO03 covers the period Aug-21 to Dec-21 and should therefore be accounted for each month.
The expected result would look something like that - note, the last column wouldn't be included in the result, it is a reference to the WOs that should be included in the COUNT.
| MONTH | COUNT OF ACTIVE Wos | WO Numbers |
| Aug-21 | 4 | WO01, WO02, WO03, WO04 |
| Sep-21 | 6 | WO03, WO04, WO05, WO06, WO07, WO08 |
| Oct-21 | 6 | WO03, WO04, WO08, WO09, WO10, WO11 |
| Nov-21 | 8 | WO03, WO04, WO08, WO10, WO11, WO12, WO13, WO14 |
| Dec-21 | 6 | WO03, WO04, WO11, WO14, WO15, WO16 |
Any help here would be greatly appreciated. I don't know how to tackle this one.
Cheers
Christine
Hi NorwoodFrog
Here is one way, I have a disconnected Date Table called MonthTable with Date, MonthYear columns
Count of Wos = VAR T1 = GENERATE ( Transactions, DATESBETWEEN (MonthTable[Date],[StartDate],[EndDate]) ) RETURN CALCULATE(COUNTROWS(VALUES(Transactions[WO_No])), ( FILTER ( T1, [Date] IN VALUES ( MonthTable[Date] ) ) )) WO Numbers = VAR T1 = GENERATE ( Transactions, DATESBETWEEN (MonthTable[Date],[StartDate],[EndDate]) ) RETURN CONCATENATEX( DISTINCT(SELECTCOLUMNS(FILTER ( T1, [Date] IN VALUES ( MonthTable[Date] ) ) ,"Wo",Transactions[WO_No])),[Wo],",")
6 Replies
Hi NorwoodFrog
Here is one way, I have a disconnected Date Table called MonthTable with Date, MonthYear columns
Count of Wos = VAR T1 = GENERATE ( Transactions, DATESBETWEEN (MonthTable[Date],[StartDate],[EndDate]) ) RETURN CALCULATE(COUNTROWS(VALUES(Transactions[WO_No])), ( FILTER ( T1, [Date] IN VALUES ( MonthTable[Date] ) ) )) WO Numbers = VAR T1 = GENERATE ( Transactions, DATESBETWEEN (MonthTable[Date],[StartDate],[EndDate]) ) RETURN CONCATENATEX( DISTINCT(SELECTCOLUMNS(FILTER ( T1, [Date] IN VALUES ( MonthTable[Date] ) ) ,"Wo",Transactions[WO_No])),[Wo],",")Hi Vera,
This works well too and is perhaps more in line with what I was trying to achieve, which is creating that virtual table of date values. I wonder though if, with a much bigger dataset like the one I'm dealing with, there would be performance issues?
Cheers
Hi NorwoodFrog
It could, if you have large table. Last time it took 1300 ms to scan 350k rows in Date table when I used DAX Studio to check which was still acceptable. Go test it:) If you can use Power Query and are willing to expand the orginal table to month level, you can do that as well.
CNENFRNLCommunity Champion
Thank You CNENFRNL, this works well. Rather advanced nesting of functions, no wonder I struggled 😉
I am wondering about performance when looking at both solutions, yours and Vera's. My dataset is really large. I understand Vera's solution better than yours tbh, but if I get it right, your CALCULATETABLE only looks at the date range for each WO, correct?
Cheers
Both solutions work and on my dataset returned a very similar performance. I can't accept both as solution, so will go with the first one.
