Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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.
Check out the July 2025 Power BI update to learn about new features.