Forum Discussion

RichOB's avatar
RichOB
Post Partisan
1 year ago
Solved

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/0...
Need Help
  • burakkaragoz's avatar
    burakkaragoz
    1 year ago

    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).
    translation and formatting supported by AI

burakkaragoz's avatar
burakkaragoz
Super User
1 year ago

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

RichOB's avatar
RichOB
Post Partisan
1 year ago

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's avatar
    burakkaragoz
    Super User
    1 year ago

    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).
    translation and formatting supported by AI