Calculate days in between two dates for each year

[Anonymous]

Hi there, 

I would like to know how you calculate days in between two dates for each year. So you could make a diagram like this :

[smpa01 · Accepted solution]

@Anonymous  you can write a meaure like this

Measure =
VAR _fact =
    FILTER (
        GENERATE ( tbl, DATESBETWEEN ( 'Calendar'[Date], tbl[Begin], tbl[End] ) ),
        [Date] <> tbl[Begin]
            && [Date] <> tbl[End]
    )
VAR _calendar =
    ADDCOLUMNS (
        'Calendar',
        "count", COUNTX ( FILTER ( _fact, [Date] = EARLIER ( 'Calendar'[Date] ) ), [Date] )
    )
RETURN
    SUMX ( _calendar, [count] )

[smpa01 · Accepted solution]

@Anonymous  you can write a meaure like this

Measure =
VAR _fact =
    FILTER (
        GENERATE ( tbl, DATESBETWEEN ( 'Calendar'[Date], tbl[Begin], tbl[End] ) ),
        [Date] <> tbl[Begin]
            && [Date] <> tbl[End]
    )
VAR _calendar =
    ADDCOLUMNS (
        'Calendar',
        "count", COUNTX ( FILTER ( _fact, [Date] = EARLIER ( 'Calendar'[Date] ) ), [Date] )
    )
RETURN
    SUMX ( _calendar, [count] )

[Anonymous]

The measure is perfect in a diagram but when a switch the same measure in a table or matrix an error occurred:

Can anyone help me with this issue?

[Anonymous]

Thank you @smpa01

[AlexisOlson · Accepted solution]

Using GENERATE on a fact table seems like a bad idea from a performance perspective. Your _fact variable could potentially be quite an enormous table.

I'd recommend something simpler like this:

DATEDIFF (
    MAX ( MIN ( tbl[Begin] ), MIN ( 'Calendar'[Date] ) ),
    MIN ( MAX ( tbl[End]   ), MAX ( 'Calendar'[Date] ) ),
    DAY
)

[Anonymous]

The measure is perfect in a diagram but when a switch the same measure in a table or matrix an error occurred:

Can anyone help me with this issue?

[AlexisOlson]

Please post the full DAX code you're using for this measure. I can't think of a way the DAX I suggested would give this error, so I'm assuming there are some extra bits.

[Anonymous]

Thank you @AlexisOlson 😁

[smpa01]

👏