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] )
``````

Did I answer your question? Mark my post as a solution!

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] )
``````

Did I answer your question? Mark my post as a solution!

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

👏

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Announcements

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

#### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

#### The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors