Create New Columns Based on Two Date Columns

[jamuka]

Dear all,

I have a table which shows promotions and their Start and End Dates. I'd like to create new "Month" columns based on promotions Start and End Dates and show how many days are in related month.

e.g for Promotion between 01 January to  12 February there will be two columns January and February and for January column value will be 31 and for February will be 12.

At the moment I create all year columns then write if formulas to show days. But I want to know wheter there is a more elegance way to write it?

Current Table

Promo ID	Forecast	Start	End
1235	50	1.01.2025	5.01.2025
1236	250	1.01.2025	31.01.2025
1237	300	1.01.2025	12.02.2025
1238	600	1.01.2025	28.02.2025
1239	900	1.01.2025	5.03.2025
1240	1000	1.01.2025	31.03.2025
1241	250	5.01.2025	31.01.2025
1242	300	5.01.2025	12.02.2025
1243	250	5.01.2025	28.02.2025
1244	300	5.01.2025	5.03.2025
1245	250	5.01.2025	31.03.2025
1246	300	1.07.2025	31.08.2025
1247	300	1.06.2025	31.08.2025
2148	300	2.02.2025	28.02.2025
2149	250	2.02.2025	5.03.2025

Updated Result

Promo ID	Forecast	Start	End	Promo Duration	January Days	February Days	January Forecast
1235	50	1.01.2025	5.01.2025	5	5	0	50
1236	250	1.01.2025	31.01.2025	31	31	0	250
1237	300	1.01.2025	12.02.2025	43	31	12	216
1238	600	1.01.2025	28.02.2025	59	31	28	315
1239	900	1.01.2025	5.03.2025	64	31	28	436
1240	1000	1.01.2025	31.03.2025	90	31	28	344
1241	250	5.01.2025	31.01.2025	27	27	0	250
1242	300	5.01.2025	12.02.2025	39	27	12	208
1243	250	5.01.2025	28.02.2025	55	27	28	123
1244	300	5.01.2025	5.03.2025	60	27	28	135
1245	250	5.01.2025	31.03.2025	86	27	28	78
1246	300	1.07.2025	31.08.2025	62	0	0	0
1247	300	1.06.2025	31.08.2025	92	0	0	0
2148	300	2.02.2025	28.02.2025	27	0	27	0
2149	250	2.02.2025	5.03.2025	32	0	27	0

My Code

let
    Source = MyFile
	
    #"Promotion Duration" = Table.AddColumn(#"Changed Type", "Promo Duration", each(1+ Duration.Days([End] - [Start])), Int64.Type),
    
	#"Added January" = Table.AddColumn(#"Promotion Duration", "January Days", each if [Start] < #date(2025, 1, 1) and [End] < #date(2025, 1, 1) then 0
else if [Start] > #date(2025, 1, 31)then 0
else if [Start] <= #date(2025, 1, 1) and [End] >= #date(2025, 1, 31) then 31
else if [Start] <= #date(2025, 1, 1) and [End] <= #date(2025, 1, 31) then (1+Duration.Days([End] - #date(2025, 1, 1))) 
else if [Start] >= #date(2025, 1, 1) and [End] <= #date(2025, 1, 31) then (1+Duration.Days([End] - [Start])) 
else if [Start] >= #date(2025, 1, 1) and [End] >= #date(2025, 1, 31) then (1+Duration.Days(#date(2025, 1, 31) - [Start])) 
else 0, Int64.Type),
    
	#"Added February" = Table.AddColumn(#"Added January", "February Days", each if [Start] < #date(2025, 2, 1) and [End] < #date(2025, 2, 1) then 0
else if [Start] > #date(2025, 2, 28) then 0
else if [Start] <= #date(2025, 2, 1) and [End] >= #date(2025, 2, 28) then 28
else if [Start] <= #date(2025, 2, 1) and [End] <= #date(2025, 2, 28) then (1+Duration.Days([End] - #date(2025, 2, 1))) 
else if [Start] >= #date(2025, 2, 1) and [End] <= #date(2025, 2, 28) then (1+Duration.Days([End] - [Start])) 
else if [Start] >= #date(2025, 2, 1) and [End] >= #date(2025, 2, 28) then (1+Duration.Days(#date(2025, 2, 28) - [Start])) 
else 0, Int64.Type),
    
	#"Added January FC" = Table.AddColumn(#"Added February", "January Forecast", each if [January Days] >0 then ([Forecast]/[Promo Duration])*[January Days]
else 0),
    
	#"Changed Type1" = Table.TransformColumnTypes(#"Added January FC",{{"January Forecast", Int64.Type}})
in
    #"Changed Type1"

kind regards

[AlienSx · Accepted solution]

let
    fx_months = (s, e) => 
        [tbl = Table.FromColumns({List.Generate(() => s, (x) => x <= e, (x) => Date.AddDays(x, 1))}, {"m"}),
        group = Table.Group(tbl, "m", {"x", Table.RowCount}, GroupKind.Local, (s, c) => Number.From(Date.Month(s) <> Date.Month(c))),
        out = Record.FromList(group[x], List.Transform(group[m], (x) => Date.ToText(x, "MMM yyyy")))][out], 
    rows = Table.TransformRows(
        your_data, 
        (x) => Table.FromRecords({x & fx_months(x[Start], x[End])})
    ), 
    result = Table.Combine(rows)
in
    result

[jamuka]

Dear @AlienSx 

thank you for your code, it works. 

But I have no idea what your code does.

If it's possible could you please explain to me or if there's a document/book that I can learn my self.

kind regards

[AlienSx]

@jamuka , my code solves your problem, that's what is does. Every M solution has some idea behind it. My idea was 

 - take your start and end dates

 - generate a list of dates from start till end

 - create a table out of this list, group this table by year/month and calculate count of rows (days), create a record of these year/month names and number of days as values

 - take each original row (record), add a record with year/month and count of days and make a table out of it

 - combine tables. 

This is pretty much it. It's must be not the very performant to my taste but.. it works.

If you want to understand what's going on in M - I would recommend to read and understand Ben Gribaudo's M Primer. Then read some book. Pay attention to The Definitive Guide to Power Query (M). Those guys are great though I don't share their extensive usage of each ( (_) => ) syntax. But that is to my taste. Don't think, buy this book and it will be a huge step for you. Don't pay much attention to YT blogers practicing PQ user interface solutions - you are limiting yourself to UI. But if UI works for you - why not?

[jamuka]

@AlienSx thank you for your detailed answer and recomendations.

My data is not big, so performance will not be a problem.