Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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:
List.Dates([Start Date], [Days Between], #duration(1,0,0,0) )
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)
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
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
Thanks. Tried to get it working from the post, no luck
PBIX attached.
https://1drv.ms/u/s!AhWg2JbSP2spicUvSi5h0UnsI09vAQ
Base data below
BOOKING NUMBER | Start Date | End Date | Duration | AMOUNT | COMPANY | DAYUSD |
2019-03-000003 | 10/03/2019 | 08/04/2019 | 30 | 30,000.00 | RED | 1000 |
2019-03-000002 | 10/03/2019 | 30/03/2019 | 21 | 42,000.00 | Blue | 2000 |
2019-03-000001 | 07/03/2019 | 30/03/2019 | 24 | 12,000.00 | Orange | 500 |
2019-02-000008 | 03/03/2019 | 31/03/2019 | 29 | 5,800.00 | Red | 200 |
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
Hi,
You may download my PBI file from here.
Hope this helps.
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
Thanks both great job and both solutions work!
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:
List.Dates([Start Date], [Days Between], #duration(1,0,0,0) )
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)
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |