Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to Solution.
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
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
Thanks! They don't necessarily use their formula, but from it I managed to get what I needed in my production environment.
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).
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
189 | |
96 | |
67 | |
63 | |
53 |