Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jamuka
Helper IV
Helper IV

Create New Columns Based on Two Date Columns

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 IDForecastStartEnd
1235501.01.20255.01.2025
12362501.01.202531.01.2025
12373001.01.202512.02.2025
12386001.01.202528.02.2025
12399001.01.20255.03.2025
124010001.01.202531.03.2025
12412505.01.202531.01.2025
12423005.01.202512.02.2025
12432505.01.202528.02.2025
12443005.01.20255.03.2025
12452505.01.202531.03.2025
12463001.07.202531.08.2025
12473001.06.202531.08.2025
21483002.02.202528.02.2025
21492502.02.20255.03.2025

 

 

Updated Result

Promo IDForecastStartEndPromo DurationJanuary DaysFebruary DaysJanuary Forecast
1235501.01.20255.01.202555050
12362501.01.202531.01.202531310250
12373001.01.202512.02.2025433112216
12386001.01.202528.02.2025593128315
12399001.01.20255.03.2025643128436
124010001.01.202531.03.2025903128344
12412505.01.202531.01.202527270250
12423005.01.202512.02.2025392712208
12432505.01.202528.02.2025552728123
12443005.01.20255.03.2025602728135
12452505.01.202531.03.202586272878
12463001.07.202531.08.202562000
12473001.06.202531.08.202592000
21483002.02.202528.02.2025270270
21492502.02.20255.03.2025320270

 

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

 

 

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

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

View solution in original post

4 REPLIES 4
AlienSx
Super User
Super User

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

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

@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?

@AlienSx thank you for your detailed answer and recomendations.

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Kudoed Authors