cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
madepassionned
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.PNG

 

2 ACCEPTED SOLUTIONS
smpa01
Super User
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 =
    ADDCOLUMNS (
        'Calendar',
        "count", COUNTX ( FILTER ( _fact, [Date] = EARLIER ( 'Calendar'[Date] ) ), [Date] )
    )
RETURN
    SUMX ( _calendar, [count] )

 

smpa01_0-1641226389928.png

 





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

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

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
)

View solution in original post

9 REPLIES 9
smpa01
Super User
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 =
    ADDCOLUMNS (
        'Calendar',
        "count", COUNTX ( FILTER ( _fact, [Date] = EARLIER ( 'Calendar'[Date] ) ), [Date] )
    )
RETURN
    SUMX ( _calendar, [count] )

 

smpa01_0-1641226389928.png

 





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

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


MicrosoftTeams-image (114).png

 

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

 

 

MicrosoftTeams-image (113).pngMicrosoftTeams-image (115).png

Can anyone help me with this issue?

Thank you @smpa01

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
)

MicrosoftTeams-image (114).png

 

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

 

 

MicrosoftTeams-image (113).pngMicrosoftTeams-image (115).png

Can anyone help me with this issue?

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.

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

Thank you @AlexisOlson 😁

👏





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

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

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.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

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

June 20 episode 7 with aka link 768x460.jpg

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