Forum Discussion
Count quantity per month
Hi!
I've been through this since trying to find help with a report I'm developing.
Basically, I have a table of records with columns ID, Start and End, where I want to count the number of active records by date (mm/yy).
As we can see, not all records have End , as they are still active on the current date.
The idea is to visualize the number of active records per month, considering the entry date and (if any) exit date.
Thanks for any help.
Hello, this works pretty well for me. It does need you to make a inactive relation ship between the date and fact table date:end date
RunningTotal_CountOfOpenRecords =TOTALYTD(COUNTROWS(Test),'Date'[Date])-CALCULATE(TOTALYTD([OpenRecordsPerMonth],'Date'[Date]),USERELATIONSHIP('Date'[Date],Test[End]))
4 Replies
Hello, something like this should work:
OpenRecords=
CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[End]<>""))Obvs, you'll need to change the table name etc. The relationship to the date table will need to be made to the Start column.
Cheers
I tried to follow this line of reasoning, but it does not accumulate monthly.
Example, considering a record with Start:2012-01-01 and End:2012-03-30. I understand that this record should be listed in months 01, 02 and 03 (when, for example, displayed in a Matrix).
Hello, this works pretty well for me. It does need you to make a inactive relation ship between the date and fact table date:end date
RunningTotal_CountOfOpenRecords =TOTALYTD(COUNTROWS(Test),'Date'[Date])-CALCULATE(TOTALYTD([OpenRecordsPerMonth],'Date'[Date]),USERELATIONSHIP('Date'[Date],Test[End]))Thanks! They don't necessarily use their formula, but from it I managed to get what I needed in my production environment.
