topic Re: Count calendar days per natural year in DAX Commands and Tips

Count calendar days per natural year

Ricardo76 — Thu, 09 Feb 2023 11:36:15 GMT

Hello,
I have a table with many records, including a start date field and an end date field:

ID	START DATE	END DATE	...more fields
1568	2023-01-01	2023-01-15
65605	2023-01-01	2023-01-08
354863	2023-02-01	2023-02-06

I want to calculate how many days per year are included between the start date and the end date of all records. in this case they would be: 21. 15 days of first row, 0 day of second row (It is already counting in the first row) and 6 days of third row.

I hope I explained myself well, thank you very much

Re: Count calendar days per natural year

jaweher899 — Thu, 09 Feb 2023 12:43:51 GMT

please try

Total Days =
SUMX(
'Table',
IF(YEAR( 'Table'[END DATE] ) = YEAR( 'Table'[START DATE] ),
DATEDIFF( 'Table'[START DATE], 'Table'[END DATE], DAY ),
DATEDIFF( 'Table'[START DATE], DATE(YEAR( 'Table'[END DATE] ), 12, 31), DAY ) +
DATEDIFF( DATE(YEAR( 'Table'[END DATE] ), 1, 1), 'Table'[END DATE], DAY )
)
)

Re: Count calendar days per natural year

tamerj1 — Thu, 09 Feb 2023 12:59:46 GMT

Hi Ricardo76
This is a crazy solution. In fact I don't expect best performance but it works. Please refer to attached sample file with the proposed solution.

This solution provides you expected total result (21) as in a card visual but shall not provide correct result at row level.

Calendar Days = VAR String = CONCATENATEX ( 'Table', CONCATENATEX ( CALENDAR ( 'Table'[START DATE], 'Table'[END DATE] ), [Date], "," ), "," ) VAR Items = SUBSTITUTE ( String, ",", "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = SELECTCOLUMNS ( T1, "@Date", PATHITEM ( Items, [Value] ) ) VAR T3 = DISTINCT ( T2 ) RETURN COUNTROWS ( T3 )

Re: Count calendar days per natural year

Ricardo76 — Thu, 09 Feb 2023 14:32:38 GMT

Thanks, but the solution doesn't work on my model. Because he is adding the days and not counting. Each year cannot add more than 365 days.

Re: Count calendar days per natural year

Ricardo76 — Thu, 09 Feb 2023 14:39:10 GMT

Crazy solution but works!!!
Thank you very much

Re: Count calendar days per natural year

tamerj1 — Fri, 10 Feb 2023 11:07:32 GMT

Ricardo76
The following solution is simpler and more efficient.

Calendar Days 2 = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( GENERATE ( 'Table', CALENDAR ( 'Table'[START DATE], 'Table'[END DATE] ) ), "@Date", [Date] ) ) )