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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kalspiros
Helper I
Helper I

Create new table based on dates

Hi all

 

Cheeky question:

Here's the table with the current information:

PROJECT IDACCEPTED DATEDEADLINEPROJECT PRICE
1234525/11/202128/11/2021£8,000
2345612/03/201511/03/2023£1,540,780

Here's the table i'm wishing we could create!

PROJECT IDDATEPRICE PER DAY (as in, [PROJECT PRICE]/([DEADLINE]-[ACCEPTED DATE])
1234525/11/2021

(8,000/(28/11/2021-25/11/2021)=8,000/4=)

£2,000

1234526/11/2021£2,000
1234527/11/2021£2,000
1234528/11/2021£2,000
2345612/03/2015

(1,540,780/(11/03/2023-12/03/2015)=1,540,780/2,921=)

£527.48

2345613/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

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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
    expandDateList

 

SUMMARY:

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:

BA_Pete_0-1637762240083.png

 

Pete

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

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
    expandDateList

 

SUMMARY:

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:

BA_Pete_0-1637762240083.png

 

Pete

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Power platform.

The moment you're suspecting that you came up with a problem that will challenge this site's gurus, the answer is being landed within 10 minutes 😄

Thank you so much @BA_Pete , that works magic, no need for taking it through SQL then!

 

Kind Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors