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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DangerHog
Frequent Visitor

create new table from begin date, end date, transactionuid

I'm not sure if this is in the correct Location...

 

I have a table of reservation data with a column for "reservation #", "begin date", "end date", and "revenue" (plus some other misc. columns).  The reservations often begin in one month and end in another month.  I need to roll up revenue per month so I want to create a new table for that contains a row for each day of the reservation and a new column for the average revenue per day.  The new table should let me slice and dice data by weekday, month, and whatever else I need. 

 

What is the easiest or best way to create the new table?

 

Thanks for your help!

 

-- Example Data --

 

Reservation#  ,  Beting Date  ,  End Date  ,  Revenue

9873459  ,  02/28/18  ,  03/03/18  ,  $14,512

2938279  ,  03/01/18  ,  03/06/18  ,  $3,235

 

 

-- Example Result Table --

Reservation#  ,  Beting Date  ,  End Date  ,  Revenue  ,  Avg. REvenue

9873459  ,  02/28/18  ,  03/03/18  ,  $14,512  ,  $3628

9873459  ,  02/28/18  ,  03/03/18  ,  $14,512  ,  $3628

9873459  ,  02/28/18  ,  03/03/18  ,  $14,512  ,  $3628

9873459  ,  02/28/18  ,  03/03/18  ,  $14,512  ,  $3628

2938279  ,  03/01/18  ,  03/06/18  ,  $3,235  ,  $539

2938279  ,  03/01/18  ,  03/06/18  ,  $3,235  ,  $539

2938279  ,  03/01/18  ,  03/06/18  ,  $3,235  ,  $539

2938279  ,  03/01/18  ,  03/06/18  ,  $3,235  ,  $539

2938279  ,  03/01/18  ,  03/06/18  ,  $3,235  ,  $539

2938279  ,  03/01/18  ,  03/06/18  ,  $3,235  ,  $539

 

1 ACCEPTED SOLUTION

@DangerHog,

 

  1. I have not taken online courses however, I have watched quite a few videos on https://www.sqlbi.com/, read a lot on https://www.daxpatterns.com/ and of course https://msdn.microsoft.com/en-us/query-bi/dax/dax-function-reference
  2. I've used a technique in Power Query the Query Editor in Power BI which may help you get started.

Using List.Transform:

 

List.Transform({Number.From([BeginDate])..Number.From([EndDate])},each Date.From(_))

 

Starting from a simple table in the Power Query Editor:

2.PNG

 

Add Column:

 

1.PNG

 

Expand the Custom Column:

 

3.PNG

 

Gets you:

 

4.PNG

 

Remove [BeginDate] & [EndDate] will get you to what I believe is your desired outcome (to start in you journey):

 

5.PNG

 

 

 

 

 

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

10 REPLIES 10
v-shex-msft
Community Support
Community Support

HI @DangerHog,

 

You can take a look at below link about expand date range with details records to new table:
Spread revenue across period based on start and end date, slice and dase this using different dates

 

After these steps, you can direct filter on detail date column to filter and group your records which different hierarchy levels.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for the reply.


I think I may just be too big of a newb to understand how that helps me.

 

1) what are the best online classes for learning DAX and how it works in Power BI?

2) Going back to the most basic concept of my problem.  How would I take a table of begin dates, end dates and Uids and create a new table that would have a column of dates and a column of UIDs where each UID is repeated for each date in the range between begin and end.

@DangerHog,

 

  1. I have not taken online courses however, I have watched quite a few videos on https://www.sqlbi.com/, read a lot on https://www.daxpatterns.com/ and of course https://msdn.microsoft.com/en-us/query-bi/dax/dax-function-reference
  2. I've used a technique in Power Query the Query Editor in Power BI which may help you get started.

Using List.Transform:

 

List.Transform({Number.From([BeginDate])..Number.From([EndDate])},each Date.From(_))

 

Starting from a simple table in the Power Query Editor:

2.PNG

 

Add Column:

 

1.PNG

 

Expand the Custom Column:

 

3.PNG

 

Gets you:

 

4.PNG

 

Remove [BeginDate] & [EndDate] will get you to what I believe is your desired outcome (to start in you journey):

 

5.PNG

 

 

 

 

 

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Hi Chris,  
is there a way, how i can get these lines, but only on level year and month? I do not need lines on daily. 
Thank you! 

 

@LindaK - I suppose you could try

List.Transform({Number.From([BeginDate])..Number.From([EndDate])},each Date.Month(_))
or
List.Transform({Number.From([BeginDate])..Number.From([EndDate])},each Date.Year(_))

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Thank you for such a quick reply, I tried this Date.Year or Date. Moth but it ended in error.  

And that error is?






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Error is: Expression.Error: The Date value must contain the Date component.
Details: 40940

 

This is the code how I write it just in case I made mistake here. (Both are dates) 

List.Transform({Number.From([first_day])..Number.From([expdate])},each Date.Month(_)))

40940 = 2/1/2012 correct?

Write as:

List.Transform({Number.From([BeginDate])..Number.From([EndDate])},each Date.Month(Date.From(_)))

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Thank you very much! That one worked! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors