Forum Discussion

hallpalmjame's avatar
hallpalmjame
New Member
7 years ago
Solved

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
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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:

     

    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

  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

     

    here is pbix file,please try it.

     

    Best Regards,

    Lin

     

6 Replies

    • hallpalmjame's avatar
      hallpalmjame
      New Member
      7 years ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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:

         

        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