Count calendar days per natural year

[Ricardo76]

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

[tamerj1]

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

[tamerj1 · Accepted solution]

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 )

[Ricardo76]

Crazy solution but works!!!
Thank you very much

[jaweher899]

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

[Ricardo76]

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.