Calculate days in between two dates for each year

[madepassionned]

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]

@madepassionned  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]

@madepassionned  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] )

[madepassionned]

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?

[madepassionned]

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
)

[madepassionned]

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.

[madepassionned]

Verplichting II = 
//MaxDatum geeft altijd de laatste dag van de geselecteerde periode
VAR MaxDatum = MAX('Dimdate 4'[Date])
//MinDatum geeft altijd de eerste dag van de geselecteerde periode
VAR MinDatum =MIN('Dimdate 4'[Date])
VAR MaxDatum_E = MAX('Dimdate 4'[Date])
VAR MinDatum_E =MIN('Dimdate 4'[Date])
Return
IF(VALUES('Tabel'[COD_BETNAT])="ZIN" && VALUES('Tabel'[Freq])="Per dag",MAX('Tabel'[NUM_ZORGVOL])*MAX('Tabel'[BED_TARIEF])*([Dagen D]/100),
//ZIN eenheid = Minuut en Frequentie = per week
IF(VALUES('Tabel'[COD_BETNAT])="ZIN" && VALUES('Tabel'[Freq])="Per week" && MAX('Tabel'[Zorgeenheid])="Minuut",((MAX('Tabel'[NUM_ZORGVOL])*(MAX('Tabel'[BED_TARIEF]))*[Dagen D])/7)/100,
//ZIN eenheid <> Minuut en Frequentie = per week
IF(VALUES('Tabel'[COD_BETNAT])="ZIN" && VALUES('Tabel'[Freq])="Per week" && MAX('Tabel'[Zorgeenheid])<>"Minuut",((MAX('Tabel'[NUM_ZORGVOL])*MAX('Tabel'[BED_TARIEF])*[Dagen D])/7)/100,
//ZIN Frequentie = per 4 weken
IF(VALUES('Tabel'[COD_BETNAT])="ZIN" && VALUES('Tabel'[Freq])="Per 4 weken",((MAX('Tabel'[NUM_ZORGVOL])*MAX('Tabel'[BED_TARIEF])*[Dagen D])/28)/100,
//ZIN Frequentie = per maand
IF(VALUES('Tabel'[COD_BETNAT])="ZIN" && VALUES('Tabel'[Freq])="Per maand",((MAX('Tabel'[NUM_ZORGVOL])*MAX('Tabel'[BED_TARIEF])*[Dagen D])/30.5)/100,
//ZIN Frequentie = per jaar
IF(VALUES('Tabel'[COD_BETNAT])="ZIN" && VALUES('Tabel'[Freq])="Per jaar"&& MAX('Tabel'[DAT_BEGIN])<=MaxDatum && MAX('Tabel'[DAT_EIND])>= MinDatum_E,((MAX('Tabel'[NUM_ZORGVOL])*MAX('Tabel'[BED_TARIEF]))/30.5)/100,
IF(VALUES('Tabel'[COD_BETNAT])="ZIN" && MAX('Tabel'[Freq])="Totaal duur beschikking" && MAX('Tabel'[Zorgeenheid])<>"Euros" && MAX('Tabel'[DAT_BEGIN])>=MinDatum && MAX('Tabel'[DAT_EIND])<= MaxDatum_E,(MAX('Tabel'[NUM_ZORGVOL])*MAX('Tabel'[BED_TARIEF]))/100,
//
IF(VALUES('Tabel'[COD_BETNAT])="ZIN" && VALUES('Tabel'[Freq])="Totaal duur beschikking" && MAX('Tabel'[Zorgeenheid])<>"Euros" && (MAX('Tabel'[DAT_BEGIN])<MinDatum || MAX('Tabel'[DAT_EIND])>= MaxDatum_E),(MAX('Tabel'[NUM_ZORGVOL])*MAX('Tabel'[BED_TARIEF])/(DATEDIFF(MAX('Tabel'[DAT_BEGIN]),MAX('Tabel'[DAT_EIND]),DAY)+1)*[Dagen D])/100,
//PGB
IF(VALUES('Tabel'[COD_BETNAT])="PGB"&& VALUES('Tabel'[Onderwerp])<>"BEGL" && VALUES('Tabel'[Freq])<>"Totaal duur beschikking"&& MAX('Tabel'[DAT_BEGIN])<=MaxDatum && MAX('Tabel'[DAT_EIND])>= MinDatum_E,(MAX('Tabel'[BED_HBH])/30.5)*(([Dagen D]+1)/100),
IF(VALUES('Tabel'[COD_BETNAT])="PGB"&& VALUES('Tabel'[Onderwerp])<>"BEGL" && VALUES('Tabel'[Freq])="Totaal duur beschikking"&& MAX('Tabel'[DAT_BEGIN])<=MaxDatum && MAX('Tabel'[DAT_EIND])>= MinDatum_E,MAX('Tabel'[BED_HBH])/100,
IF(VALUES('Tabel'[COD_BETNAT])="PGB"&& VALUES('Tabel'[Onderwerp])="BEGL" && VALUES('Tabel'[DAT_BEGIN])<=MaxDatum && MAX('Tabel'[DAT_EIND])>= MinDatum_E,(MAX('Tabel'[BED_HBH])/30.5)*(([Dagen D]+1)/100),
//Euros
IF(MAX('Tabel'[Zorgeenheid])="Euros" && MAX('Tabel'[DAT_BEGIN])<=MaxDatum && MAX('Tabel'[DAT_EIND])>= MinDatum_E,MAX('Tabel'[NUM_ZORGVOL])/100,
//
IF(MAX('Tabel'[DAT_BEGIN])<=MaxDatum && MAX('Tabel'[DAT_EIND])>= MinDatum_E,MAX('Tabel'[BED_LST])/100,0)))))))))))))

[madepassionned]

Thank you @AlexisOlson 😁

[smpa01]

👏