topic Calculate days in between two dates for each year in DAX Commands and Tips

Calculate days in between two dates for each year

Anonymous — Mon, 03 Jan 2022 15:43:24 GMT

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 :

Re: Calculate days in between two dates for each year

smpa01 — Mon, 03 Jan 2022 16:13:20 GMT

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

Re: Calculate days in between two dates for each year

AlexisOlson — Mon, 03 Jan 2022 17:30:31 GMT

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 )

Re: Calculate days in between two dates for each year

smpa01 — Mon, 03 Jan 2022 17:33:15 GMT

👏

Re: Calculate days in between two dates for each year

Anonymous — Mon, 03 Jan 2022 17:57:39 GMT

Thank you smpa01

Re: Calculate days in between two dates for each year

Anonymous — Mon, 03 Jan 2022 17:58:44 GMT

Thank you AlexisOlson 😁

Re: Calculate days in between two dates for each year

Anonymous — Thu, 06 Jan 2022 16:14:10 GMT

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?

Re: Calculate days in between two dates for each year

Anonymous — Thu, 06 Jan 2022 16:14:33 GMT

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?

Re: Calculate days in between two dates for each year

AlexisOlson — Thu, 06 Jan 2022 16:57:00 GMT

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.