Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have an interesting issue. I have a client that essentially wants me to spread the period cost value over from the start month to the end of the current year. Thus, we have the following:
Start Date: 04/01/2024
End Date: 10/31/2025
Peirod Cost: 86480
The client is only budgeting during the current year (2024). Thus, I need to take the 86480 and divide it by the number of months between 04/01/2024 and 10/31/2025 which is 19 months including the current. However, the issue, is that I need to take 4551 and put it into buckets / rows. Thus, in theory, something that would look like
etc up to December 2024. Not sure how to really do that. Any help would be appreciated. Maybe I need to do this in SQL instead of DAX?
Thanks
Solved! Go to Solution.
No worries.
Yes, the Json.Document step was generated by using the Enter Data function in the Power Query user interface in order to input your sample row, however I realise that this obscures the intent.
Here is a version using the #table constructor instead (PBIX attached too).
This code can be pasted into a blank query in the Power Query advanced editor to see the steps.
let
Source =
#table(
type table[PlanID = text, StartDate = date, EndDate = date, PeriodCost = Currency.Type],
{
{ "E28B8051205F462282A572F6DA375D5D", #date(2024,4,1), #date(2025,10,31), 4551.00 }
}
),
#"Added NumPeriods" = Table.AddColumn(
Source,
"NumPeriods",
each (Date.Year([EndDate]) - Date.Year([StartDate]))
* 12 + Date.Month([EndDate]) - Date.Month([StartDate]) + 1,
Int64.Type
),
#"Added Date List" = Table.AddColumn(
#"Added NumPeriods",
"Date",
each
let
StartDate = [StartDate]
in
List.Transform(
{0 .. [NumPeriods] - 1},
each Date.AddMonths(Date.StartOfMonth(StartDate), _)
),
type {date}
),
#"Expanded Date List" = Table.ExpandListColumn(#"Added Date List", "Date"),
#"Removed Columns" = Table.RemoveColumns(
#"Expanded Date List",
{"NumPeriods", "StartDate", "EndDate"}
),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Date", "StartDate"}}),
#"Added EndDate" = Table.AddColumn(
#"Renamed Columns",
"EndDate",
each Date.EndOfMonth([StartDate]),
type date
)
in
#"Added EndDate"
Thanks! I will check this out.
If we assume that you're always dealing in whole periods that each receive the same cost allocation, here's a simple example in Power Query (I would not recommend DAX for this sort of thing):
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"JcexDcAgDATAXVzHkv34wS0RsARi/zWSKNI1t7dM5J1Gh3FFBRKdDauOXhoHh1wCQ6i9/A/VTcuXIF3OeQA=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [PlanID = _t, StartDate = _t, EndDate = _t, PeriodCost = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{
{"PlanID", type text},
{"StartDate", type date},
{"EndDate", type date},
{"PeriodCost", Currency.Type}
}
),
#"Added NumPeriods" = Table.AddColumn(
#"Changed Type",
"NumPeriods",
each (Date.Year([EndDate]) - Date.Year([StartDate]))
* 12 + Date.Month([EndDate]) - Date.Month([StartDate]) + 1,
Int64.Type
),
#"Added Date List" = Table.AddColumn(
#"Added NumPeriods",
"Date",
each
let
StartDate = [StartDate]
in
List.Transform(
{0 .. [NumPeriods] - 1},
each Date.AddMonths(Date.StartOfMonth(StartDate), _)
),
type {date}
),
#"Expanded Date List" = Table.ExpandListColumn(#"Added Date List", "Date"),
#"Removed Columns" = Table.RemoveColumns(
#"Expanded Date List",
{"NumPeriods", "StartDate", "EndDate"}
),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Date", "StartDate"}}),
#"Added EndDate" = Table.AddColumn(
#"Renamed Columns",
"EndDate",
each Date.EndOfMonth([StartDate]),
type date
)
in
#"Added EndDate"
Note that you may only require one of StartDate or EndDate in the model.
If pushing upstream to SQL is an option, then that would probably be preferable.
Someone may want to chime in with the equivalent transformation in SQL 🙂
Regards
Thanks! Yes, maybe in SQL might be better because this is a bit over my head for the Power Query. Is the JSON consuming the one row in my example?
No worries.
Yes, the Json.Document step was generated by using the Enter Data function in the Power Query user interface in order to input your sample row, however I realise that this obscures the intent.
Here is a version using the #table constructor instead (PBIX attached too).
This code can be pasted into a blank query in the Power Query advanced editor to see the steps.
let
Source =
#table(
type table[PlanID = text, StartDate = date, EndDate = date, PeriodCost = Currency.Type],
{
{ "E28B8051205F462282A572F6DA375D5D", #date(2024,4,1), #date(2025,10,31), 4551.00 }
}
),
#"Added NumPeriods" = Table.AddColumn(
Source,
"NumPeriods",
each (Date.Year([EndDate]) - Date.Year([StartDate]))
* 12 + Date.Month([EndDate]) - Date.Month([StartDate]) + 1,
Int64.Type
),
#"Added Date List" = Table.AddColumn(
#"Added NumPeriods",
"Date",
each
let
StartDate = [StartDate]
in
List.Transform(
{0 .. [NumPeriods] - 1},
each Date.AddMonths(Date.StartOfMonth(StartDate), _)
),
type {date}
),
#"Expanded Date List" = Table.ExpandListColumn(#"Added Date List", "Date"),
#"Removed Columns" = Table.RemoveColumns(
#"Expanded Date List",
{"NumPeriods", "StartDate", "EndDate"}
),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Date", "StartDate"}}),
#"Added EndDate" = Table.AddColumn(
#"Renamed Columns",
"EndDate",
each Date.EndOfMonth([StartDate]),
type date
)
in
#"Added EndDate"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |