Forum Discussion
Create new table based on dates
Hi all
Cheeky question:
Here's the table with the current information:
| PROJECT ID | ACCEPTED DATE | DEADLINE | PROJECT PRICE |
| 12345 | 25/11/2021 | 28/11/2021 | £8,000 |
| 23456 | 12/03/2015 | 11/03/2023 | £1,540,780 |
Here's the table i'm wishing we could create!
| PROJECT ID | DATE | PRICE PER DAY (as in, [PROJECT PRICE]/([DEADLINE]-[ACCEPTED DATE]) |
| 12345 | 25/11/2021 | (8,000/(28/11/2021-25/11/2021)=8,000/4=) £2,000 |
| 12345 | 26/11/2021 | £2,000 |
| 12345 | 27/11/2021 | £2,000 |
| 12345 | 28/11/2021 | £2,000 |
| 23456 | 12/03/2015 | (1,540,780/(11/03/2023-12/03/2015)=1,540,780/2,921=) £527.48 |
| 23456 | 13/03/2015 | £527.48 |
| .... |
Just to point out that i have no real desire on creating such a table on power query or something. If that can be done in DAX, no problem at all, i'd welcome that. Plus, it would probably make sense for the DATE granularity to be on months rather than individual days but that would create the pro rata unneeded headache for projects that might just start on the last day of a month.
The main purpose of this exercise is to allow managers to review the potential revenue generated from projects long into the future by assuming a linear distribution of overall project price.
What do we think?
Many thanks in advance
Hi kalspiros ,
In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcuxDcBACEPRXaiRwHAk7IJuumySyQJXpfzWcxXBfAUxWQggpoaJ/MX7JKsqbS4afPUGE/UGmGvTE+ZHg2Mp39mP/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PROJECT ID" = _t, #"ACCEPTED DATE" = _t, DEADLINE = _t, #"PROJECT PRICE" = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"PROJECT ID", Int64.Type}, {"ACCEPTED DATE", type date}, {"DEADLINE", type date}, {"PROJECT PRICE", Currency.Type}}), addDaysBetween = Table.AddColumn(chgTypes, "daysBetween", each Duration.Days([DEADLINE] - [ACCEPTED DATE]) + 1), addDailyPrice = Table.AddColumn(addDaysBetween, "dailyPrice", each [PROJECT PRICE] / [daysBetween]), addDateList = Table.AddColumn(addDailyPrice, "dateList", each List.Transform({Number.From([ACCEPTED DATE])..Number.From([DEADLINE])}, each Date.From(_))), expandDateList = Table.ExpandListColumn(addDateList, "dateList") in expandDateListSUMMARY:
1) Get days between dates
2) Divide [PROJECTPRICE] by days to get daily value
3) Create list of dates between accpted/deadline dates
4) Expand list
This give me the following output:
Pete
2 Replies
Hi kalspiros ,
In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcuxDcBACEPRXaiRwHAk7IJuumySyQJXpfzWcxXBfAUxWQggpoaJ/MX7JKsqbS4afPUGE/UGmGvTE+ZHg2Mp39mP/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PROJECT ID" = _t, #"ACCEPTED DATE" = _t, DEADLINE = _t, #"PROJECT PRICE" = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"PROJECT ID", Int64.Type}, {"ACCEPTED DATE", type date}, {"DEADLINE", type date}, {"PROJECT PRICE", Currency.Type}}), addDaysBetween = Table.AddColumn(chgTypes, "daysBetween", each Duration.Days([DEADLINE] - [ACCEPTED DATE]) + 1), addDailyPrice = Table.AddColumn(addDaysBetween, "dailyPrice", each [PROJECT PRICE] / [daysBetween]), addDateList = Table.AddColumn(addDailyPrice, "dateList", each List.Transform({Number.From([ACCEPTED DATE])..Number.From([DEADLINE])}, each Date.From(_))), expandDateList = Table.ExpandListColumn(addDateList, "dateList") in expandDateListSUMMARY:
1) Get days between dates
2) Divide [PROJECTPRICE] by days to get daily value
3) Create list of dates between accpted/deadline dates
4) Expand list
This give me the following output:
Pete
