cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hallpalmjame Frequent Visitor
Frequent Visitor

Distributing booking value across start and end dates

Hi I am a Power BI novice and am trying to distribute revenue over a time period. I looked at a couple of posts, but can not replicate the solutions. 

Have created a dates table DimDates, and am trying to create a column TotalDaysSales in DimDates table that sums up the average daily booking value of bookings which are valid on that date. To do this have created a column in the Bookings table called [DAYUSD] which works out the average booking value per day that the booking is valid for (this works). And then thought I could simply calculate off the back of that something like

 

TotalDaysSales = CALCULATE(SUM(Bookings[DAYUSD]),
        FILTER(Bookings,
                COUNTROWS(FILTER(VALUES(DimDates[Date]),
                        Bookings[startdate]<=DimDates[Date] &&
                        Bookings[enddate]>=DimDates[Date] ))
        >0))
 
But not working due to my current limited knowledge. Any help appreciated! Can share the file
 
 
2 ACCEPTED SOLUTIONS

Accepted Solutions
Nick_M Senior Member
Senior Member

Re: Distributing booking value across start and end dates

This can be done with dax, but would have to mix filters and relationships, etc. So if you arent opposed to using Power Query, this can be done pretty easily.  I attached the pbix file below, but basically it is doing:

  1. Find the amount of days between start and end ( you have a column in there already, but wanted to make this work with whatever future data you may have)
  2. Add a custom column that produces a list of dates beteen that start and end date
List.Dates([Start Date], [Days Between], #duration(1,0,0,0) )
  1. Divide out the USD amount and the days between from #1 above. Again, you had this in there, but wanted to be sure it would work in the future
  2. Remove the misc columns we no longer need
  3. Expand the list of dates. So this will have the average value per day
  4. Relate the DimDate to this table ( also, be sure to mark the date table as a Date Table in the Data view, also be sure to sort the Month Name by the Month Number column)

 

Then just a simple sum formula:

Easy Sum = sum( Table1[Avg Per Day] )

And the final table:

Final Table.png

 

Here's the file so you can step through the applied steps (  you can ignore the first couple as I was having trouble with the dates)

https://1drv.ms/u/s!Amqd8ArUSwDS0ED7IECT7MD60OHq

Community Support Team
Community Support Team

Re: Distributing booking value across start and end dates

hi, @hallpalmjame 

Try this way as below:

Create a new table

Table = FILTER(CROSSJOIN(Bookings,DimDates),Bookings[StartDate]<='DimDates'[Date]&&Bookings[EndDate]>='DimDates'[Date])

Then drag date and DAYUSD from New table

5.JPG

 

here is pbix file,please try it.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Community Support Team
Community Support Team

Re: Distributing booking value across start and end dates

hi, @hallpalmjame 

You may refer to this similar post:

https://community.powerbi.com/t5/Desktop/Measuring-logged-workdays/m-p/613517#M292418

 

If not your case,Please share sample data and expected output. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hallpalmjame Frequent Visitor
Frequent Visitor

Re: Distributing booking value across start and end dates

Thanks. Tried to get it working  from the post, no luck


PBIX attached.

 

https://1drv.ms/u/s!AhWg2JbSP2spicUvSi5h0UnsI09vAQ

 

Base data below

BOOKING NUMBERStart DateEnd DateDurationAMOUNTCOMPANYDAYUSD
2019-03-00000310/03/201908/04/20193030,000.00RED1000
2019-03-00000210/03/201930/03/20192142,000.00Blue2000
2019-03-00000107/03/201930/03/20192412,000.00Orange500
2019-02-00000803/03/201931/03/2019295,800.00Red200

 

Should translate to on the Distributed Sheet

February 81800 USD

April    8000 USD


As only the first order has any days in April, a total of 8 days for 1000 USD a day

 

Any help appreciated

 

Nick_M Senior Member
Senior Member

Re: Distributing booking value across start and end dates

This can be done with dax, but would have to mix filters and relationships, etc. So if you arent opposed to using Power Query, this can be done pretty easily.  I attached the pbix file below, but basically it is doing:

  1. Find the amount of days between start and end ( you have a column in there already, but wanted to make this work with whatever future data you may have)
  2. Add a custom column that produces a list of dates beteen that start and end date
List.Dates([Start Date], [Days Between], #duration(1,0,0,0) )
  1. Divide out the USD amount and the days between from #1 above. Again, you had this in there, but wanted to be sure it would work in the future
  2. Remove the misc columns we no longer need
  3. Expand the list of dates. So this will have the average value per day
  4. Relate the DimDate to this table ( also, be sure to mark the date table as a Date Table in the Data view, also be sure to sort the Month Name by the Month Number column)

 

Then just a simple sum formula:

Easy Sum = sum( Table1[Avg Per Day] )

And the final table:

Final Table.png

 

Here's the file so you can step through the applied steps (  you can ignore the first couple as I was having trouble with the dates)

https://1drv.ms/u/s!Amqd8ArUSwDS0ED7IECT7MD60OHq

Community Support Team
Community Support Team

Re: Distributing booking value across start and end dates

hi, @hallpalmjame 

Try this way as below:

Create a new table

Table = FILTER(CROSSJOIN(Bookings,DimDates),Bookings[StartDate]<='DimDates'[Date]&&Bookings[EndDate]>='DimDates'[Date])

Then drag date and DAYUSD from New table

5.JPG

 

here is pbix file,please try it.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hallpalmjame Frequent Visitor
Frequent Visitor

Re: Distributing booking value across start and end dates

Thanks both great job and both solutions work!

 

Super User
Super User

Re: Distributing booking value across start and end dates

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png