Calculate number of days between two days and put in respective months

[madlfc87]

Hi everyone,

I'm having difficulties calculating the number of days between two dates because I need the days to be counted for each seperate month. For example if the two dates are in the same month then it's easy.

Table.AddColumn(Source, "Subtraction", each Duration.Days([EndDate] - [StartDate])+1, Int64.Type)

But when the dates are in different months i need to calculate the days that fall in each month seperately. 

Please see below a part of the two date columns 

Thank you for your help!

[bhanu_gautam]

@madlfc87 , Try using below m code

let
Source = ... , // Your data source
AddDateList = Table.AddColumn(Source, "DateList", each List.Dates([StartDate], Duration.Days([EndDate] - [StartDate]) + 1, #duration(1, 0, 0, 0))),
ExpandDateList = Table.ExpandListColumn(AddDateList, "DateList"),
AddYearMonth = Table.AddColumn(ExpandDateList, "YearMonth", each Date.Year([DateList]) * 100 + Date.Month([DateList]), Int64.Type),
GroupByYearMonth = Table.Group(AddYearMonth, {"YearMonth"}, {{"DaysInMonth", each Table.RowCount(_), Int64.Type}})
in
GroupByYearMonth

[madlfc87]

I will need to create this visualization so i would need a table with month, year, location, days 

[madlfc87]

https://drive.google.com/file/d/1mY3Sh23MGMPULASau0uYKayL4OUwR10i/view?usp=drive_link

This is the file. Could you assist me? I didn't fully understand your code. If you want I will give you access. Thank you