how to calculate no of days in each month between startdate and enddate

[snehar]

Hi everybody I am newbie with DAX.

I have a vacation table, with startdate, enddate and employee id. I need to calculate no of days in each month(if start date and endate are two different months)
for example
id  start_date   end_date
10 17/05/2023 12/06/2023
I need to know howmany days in may and june separately
Any help would be much appreciated. Thanks all!

[snehar]

hey , is it possible to get no of days in each month between startdate and enddate, as a new column in the same table?

[Ashish_Mathur]

No, it is not.  If the start and end dates span multiple months then one rows for each day should be created.  You cannot create a row in a Table.

[snehar]

if there is multiple months like the screenshot i shared, of diiferent employees (startdate and enddate of vactaion taken of each employee)how do i get no of days in each month 

[Ashish_Mathur]

The only way i am aware of is to transform the data in the Query Editor (I have already shared that file with you)

[snehar]

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached my PBI file.

Hope this helps.

[Anonymous]

Total_Days=DateDiff(Min(Start_Date) , Max(End_Date),Day)