Forum Discussion
Day Count Measure
Hi, using the table below, I need the number of days that each Room was empty per month, please:
| Location | Room | Empty_Start_Date | Empty_End_Date | Days_Empty |
| Edinburgh | E1 | 01/04/2024 | 24/04/2024 | 24 |
| Edinburgh | E2 | 01/04/2024 | 31/05/2024 | 60 |
| Glasgow | G1 | 01/04/2024 | 30/07/2024 | 120 |
| Glasgow | G2 | 01/04/2024 | 01/06/2024 | 61 |
This is the end result I'm looking for :
| Room | April | May | June | July |
| E1 | 24 | |||
| E2 | 30 | 31 | ||
| G1 | 30 | 31 | 30 | |
| G2 | 30 | 31 | 1 | 30 |
| 114 | 93 | 31 | 30 |
**my monthly numbers may be a tad wrong here.
Thanks
If your DaysOpen measure is showing blank in a card, it might be because there's no row context — try wrapping it like this:
DaysOpen = CALCULATE( DATEDIFF(MIN('Table'[Start_Date]), TODAY(), DAY) )Now for the line graph part — if you want to show how many items are still open per month, you’ll need to:
- Create a Calendar table with a full date range.
- Create a relationship from Calendar[Date] to Table[Start_Date].
- Use a measure like this:
OpenItems = CALCULATE( COUNTROWS('Table'), 'Table'[Start_Date] <= MAX('Calendar'[Date]) )This will count how many items were open as of each date in the calendar, and you can plot that over time.
Let me know if you want to include End_Date logic too (like only count items that haven’t been closed yet).
translation and formatting supported by AI
5 Replies
Hi RichOB ,
You can create a measure like this to count the number of days between two dates:
DayCount = DATEDIFF(MIN('Table'[StartDate]), MAX('Table'[EndDate]), DAY)If you're working with filters or slicers, make sure the context is right — sometimes wrapping it in a CALCULATE or using SELECTEDVALUE helps depending on your setup.
Let me know if you’re trying to count working days or exclude weekends/holidays — that’s a slightly different approach.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AIHi burakkaragoz thanks for getting back to me, this is great thank you! How would the measure change if there is no end date and I want the current Today's date as the MAX date please?
Hi RichOB ,
If you want the day count just for the selected row (not across the whole table), you can use something like this:
DayCount = DATEDIFF( SELECTEDVALUE('Table'[StartDate]), SELECTEDVALUE('Table'[EndDate]), DAY )This way, it only calculates the difference for the current row in the visual. If you're using it in a card or matrix, it should now behave as expected.
Let me know if you're working with multiple rows or need to handle blanks — happy to tweak it further!
translation and formatting supported by AI
