Forum Discussion

NorwoodFrog's avatar
NorwoodFrog
Regular Visitor
4 years ago
Solved

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 f...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
NorwoodFrog
Regular Visitor
4 years ago

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