How to count occupation Dates

Hi JohnSpartan,

You can refer to below sample to achieve your requirement.

1. Create a calendar table based on record table.

CALENDAR = CALENDAR(FIRSTDATE(Records[Date From]),LASTDATE(Records[To]))

2. Write a measure to calculate the occupation date count.

Dynamic Count = 
VAR current_Date =
    MAX ( 'CALENDAR'[Date] )
VAR stare_date =
    DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 1 )
VAR end_date =
    DATE ( YEAR ( current_Date ), MONTH ( current_Date ) + 1, 1 )
        - 1
VAR filtered =
    FILTER (
        ALL ( Records ),
        CONTAINS (
            ADDCOLUMNS (
                CALENDAR ( [Date From], [To] ),
                "YearMonth", FORMAT ( [Date], "mmm/yyyy" )
            ),
            [YearMonth], FORMAT ( current_Date, "mmm/yyyy" )
        )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                filtered,
                "StartDat", IF ( [Date From] <= stare_date, stare_date, [Date From] ),
                "EndDate", IF ( [To] >= end_date, end_date, [To] )
            ),
            "Diff", DATEDIFF ( [StartDat], [EndDate], DAY )
        ),
        [Diff]
    )

3. Use measure and calendar date to create visuals.

Comment:

VAR stare_date =
DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 1 )
VAR end_date =
DATE ( YEAR ( current_Date ), MONTH ( current_Date ) + 1, 1 )
- 1
find out the startdate and enddate of current month.

VAR filtered =
FILTER (
ALL ( Records ),
CONTAINS (
ADDCOLUMNS (
CALENDAR ( [Date From], [To] ),
"YearMonth", FORMAT ( [Date], "mmm/yyyy" )
),
[YearMonth], FORMAT ( current_Date, "mmm/yyyy" )
)
)
filter related records based on year month of current date.

ADDCOLUMNS (
ADDCOLUMNS (
filtered,
"StartDat", IF ( [Date From] <= stare_date, stare_date, [Date From] ),
"EndDate", IF ( [To] >= end_date, end_date, [To] )
),
"Diff", DATEDIFF ( [StartDat], [EndDate], DAY )
)
Dynamic compare the current record date with variable start_date/end_date, add columns to store these correct date and calculate the date diff.

Notice: I attach the pbix file below.

Regards,

Xiaoxin Sheng

Hi,

You may refer to my solution here.

Hope this helps.