Calculate days between dates that cross months

[Anonymous]

Hi, 

I'm at an impass, I've got a table with the start and end date and a column that identifes the number of dates in between.

I've written the following measure

Absence Days in Mth =

VAR firstDayOfMonth =

    MIN ( 'DateDimension'[Date] )

VAR lastDayOfMonth =

    MAX ( 'DateDimension'[Date]  )

RETURN

    SUMX (

        Absence,

        VAR s =

            MAX ( Absence[StartDate], firstDayOfMonth )

        VAR e =

            MIN (Absence[EndDate], lastDayOfMonth )

        RETURN

            IF ( s < e, DATEDIFF ( s-1, e, DAY ) )

    )

When I put this measure as a card on my report and then use the Month (date Table) as a filter I get the correct value for March of 4. For April I get 30 rather than 30 + 22 from the first row.

My date table is joined to the absence table on date - startdate.

How do I get this so it picks up the April figure of the first record when I filter for April.

TIA

[CNENFRNL · Accepted solution]

In general, calendar table in such inteval calculations doesn't function as a dimension like other date-oriented analysis.

Tricky solution to the tricky question,

[Anonymous]

Thank you, thats worked perfectly.