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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!