Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi @RichaBansal
With that exact data no, but if the Start and End Columns were formated as dates, then yes.
Date.Year([End])*12+Date.Month([End])-Date.Year([Start])*12-Date.Month([Start])
[Amount]/ [Custom]
List.Numbers(1,[Custom])
Date.StartOfMonth(
Date.AddMonths(
[Start],
[Intervals] -1
)
))
This should help
Joe
If you found my answer helpful and it solved your issue, please accept as solution
Proud to be a Super User! | |
Date tables help! Learn more
I think I missed a step. You will need to expand the list to new rows before adding the last step
Proud to be a Super User! | |
Date tables help! Learn more
Hi @RichaBansal
With that exact data no, but if the Start and End Columns were formated as dates, then yes.
Date.Year([End])*12+Date.Month([End])-Date.Year([Start])*12-Date.Month([Start])
[Amount]/ [Custom]
List.Numbers(1,[Custom])
Date.StartOfMonth(
Date.AddMonths(
[Start],
[Intervals] -1
)
))
This should help
Joe
If you found my answer helpful and it solved your issue, please accept as solution
Proud to be a Super User! | |
Date tables help! Learn more
Hi Joe,
The start and end are dates. I got the list of numbers, but the Last column shows error.
I think I missed a step. You will need to expand the list to new rows before adding the last step
Proud to be a Super User! | |
Date tables help! Learn more
Worked like a charm 🙂
Thank you Joe. Have a pleasant day.
This will work only in case when Start and End are defined as dates. @RichaBansal, let us know if input Start and End are dates or just text "Jan" and "April"
@RichaBansal, in case you have input stored as text, try this:
Result:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRciwoyswB0oYGIKAUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t, Amount = _t]),
Transformed = Table.TransformColumns(Source, {
{"Start", each Date.Month(Date.FromText("2024" & Text.Start(_,3) & "01", [Format="yyyyMMMdd", Culture="en-US"])), Int64.Type},
{"End", each Date.Month(Date.FromText("2024" & Text.Start(_,3) & "01", [Format="yyyyMMMdd", Culture="en-US"])), Int64.Type},
{"Amount", Number.From, type number}
}),
Ad_FinalTable = Table.AddColumn(Transformed, "FinalTable", each
[ a = [Amount] / ([End]-[Start]+1),
b = List.Transform({[Start]..[End]}, (x)=> Date.ToText(#date(2024,x,1), "MMM", "en-US")),
c = Table.FromColumns({ b, List.Repeat({a}, List.Count(b)) }, type table[Month=text, Amount=number])
][c], type table),
FinalTable = Table.Combine(Ad_FinalTable[FinalTable])
in
FinalTable
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |