Counting objects/month based on Start and End Date

[NorwoodFrog]

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 

[Vera_33 · Accepted solution]

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],",")

[NorwoodFrog]

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. 

[CNENFRNL · Accepted solution]

[NorwoodFrog]

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

[Vera_33 · Accepted solution]

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],",")

[NorwoodFrog]

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

[Vera_33]

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.