Forum Discussion
Counting objects/month based on Start and End Date
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 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.
