Day Count Measure

[RichOB]

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

[burakkaragoz]

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.

[RichOB]

Hi @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?

[burakkaragoz]

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!

[RichOB]

I'm not sure this is what I'm looking for (maybe it is and I don't understand it! sorry haha). For some reason, this is showing as blank on a card. I have the running total now from this measure:

DaysOpen = DateDiff ('Table'[Start_Date], TODAY (), DAY)

How could I connect this to a line graph? When I try to join the calendar to the Table it asks to join to the Table Start_Date or End_Date but I don't want that as it would give incorrect figures. How do I structure hte calendar to show the correct days open total per month? 

[burakkaragoz · Accepted solution]

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:

1. Create a Calendar table with a full date range.
2. Create a relationship from Calendar[Date] to Table[Start_Date].
3. 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).