Create column that calculates the days based on period in between dates

[TeunVerhagen]

Hi all,

For a project I have to create a table that shows the number of days a contract is active per month. I manually created a table as an example (see below). The first two rows are manually filled in to make things clear. The bold columns are the ones that I want to add based on a formula.

Each contract has a start date and an end date. The formula must return the number of days that the contract is active based on the period between start date and end date. So for example a contract with start date 1-1-2020 and end date 5-2-2020 is 31 days in Januari and 5 days in Februari.

Can someone help with the formula that I have to use to create the month columns?

Contract	Start date	End date	Days in jan-20	Days in feb-20	Days in mar-20
1	1-1-2020	12-2-2020	31	12	0
2	10-1-2020	31-3-2020	10	29	31
3	5-2-2020	25-3-2020	?	?	?

Thanks in advance!

Teun

[dax · Accepted solution]

Hi @TeunVerhagen , 

I think the result should be similar to below

Contract	Start date	End date	Days in jan-20	Days in feb-20	Days in mar-20
1	1-1-2020	12-2-2020	31	12	0
2	10-1-2020	31-3-2020	22(here should be 22)	29	31
3	5-2-2020	25-3-2020	0	25	25

If so , you could try below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYrBCQAwCMR2ubeCnjiNuP8abSmtv5CkCg6BqyuNdpDKyy0FHmNTwzWmxjb5dgHzx14=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Contract = _t, #"Start date" = _t, #"End date" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start date", type date},{"End date", type date}}, "aa-DJ"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each {Number.From([Start date])..Number.From([End date])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each Text.From (Date.Year([Custom]))&" "& Date.MonthName([Custom])),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Contract", "Start date", "End date", "Custom.1"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Custom.1]), "Custom.1", "Count", List.Sum)
in
    #"Pivoted Column"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @TeunVerhagen ,

Take a look on this file: Download PBIX 

It creates a table with the date range for each project and count the dates for each month.

If you consider it as a solution, please mark as a solution and kudos.

Ricardo