Holidays in Period - Correct month

[Dnietota]

Hello everyone!

I have a dataset of holidays whith every employee petition as a row. In the dataset there are also two dates and the number of days between those dates.

• Starting Date: The first day of the holidays
• Ending Date: The last day of the holidays.

I would like to calculate the amount of holidays requested for each month but i'm facing some issues. Something similar to the manually collumn I worte below in "Comments" column.

• If the start and end date are in the same month, I have no problems, all days correspond to the same month. 
• If the start date month does not match the ending month, then I need to divide them and make the calculation correct. 

Absence	Employee	Starting Date	Ending Date	Amount	Comments
Holidays	1023	29/05/2023	29/05/2023	1	May
Holidays	1023	29/05/2023	30/05/2023	2	May
Holidays	1012	29/05/2023	31/05/2023	3	May
Holidays	1065	29/05/2023	02/06/2023	5	3 days in May 2 in June
Holidays	1065	29/05/2023	02/06/2023	5	3 days in May 2 in June
Holidays	1061	31/05/2023	02/06/2023	3	1 day in May, 2 in June
Holidays	1005	09/06/2023	09/06/2023	1	June
Holidays	1043	09/06/2023	09/06/2023	1	June
Holidays	1004	24/07/2023	28/07/2023	5	July
Holidays	1004	24/07/2023	28/07/2023	5	July
Holidays	1012	31/07/2023	31/07/2023	1	July
Holidays	1012	31/07/2023	04/08/2023	5	1 day in July, 4 in August
Holidays	1023	31/07/2023	04/08/2023	5	1 day in July, 4 in August
Holidays	1045	03/08/2023	04/08/2023	2	August

Can someone please help me? I'm stucked with this... find attached the PBI file.

Regards,

[ThxAlot]

Generic solution

SPAN Detail.pbix

[Ritaf1983]

Hi @Dnietota 
unfortunately, there is no access to your file so I used a table that you attached.
You can achieve your goal with 2 calculated columns :
1. For delta between the days :

dated diff = DATEDIFF('Table'[Starting Date],'Table'[Ending Date],DAY)+1

2 for desired comments :

comments_ =

IF (

    DATEDIFF ( 'Table'[Starting Date], 'Table'[Ending Date], MONTH ) + 1 = 1,

    'Table'[Starting Date].[Month],

    DATEDIFF ( 'Table'[Starting Date], ENDOFMONTH ( 'Table'[Starting Date] ), DAY ) + 1 & " IN "

        & FORMAT ( 'Table'[Starting Date].[Date], "MMMM" ) & ", "

        & 'Table'[dated diff]

            - DATEDIFF ( 'Table'[Starting Date], ENDOFMONTH ( 'Table'[Starting Date] ), DAY ) - 1 & " IN "

        & FORMAT ( 'Table'[Ending Date].[Date], "MMMM" )

)

Link to pbix with the solution 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickl