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

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.