cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate days in between two dates for each year

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 :

2 ACCEPTED SOLUTIONS
Super User

@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 =
'Calendar',
"count", COUNTX ( FILTER ( _fact, [Date] = EARLIER ( 'Calendar'[Date] ) ), [Date] )
)
RETURN
SUMX ( _calendar, [count] )
``````

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Super User

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
)
``````
9 REPLIES 9
Super User

@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 =
'Calendar',
"count", COUNTX ( FILTER ( _fact, [Date] = EARLIER ( 'Calendar'[Date] ) ), [Date] )
)
RETURN
SUMX ( _calendar, [count] )
``````

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Frequent Visitor

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?

Frequent Visitor

Thank you @smpa01

Super User

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
)
``````
Frequent Visitor

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?

Super User

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.

Frequent Visitor
``````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)))))))))))))``````
Frequent Visitor

Thank you @AlexisOlson 😁

Super User

👏

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Announcements