Forum Discussion
Split Revenue per Month
Hi all
Its more like a generel data question, but im wondering if Power BI may be able to solve it easyli.
I have a orderlines table with columns "From" and "Till".
Lets say one orderline generates 1000 revenue, but it is from week 8 - 9 (18.2.19 - 03.3.19), how can i split the revenue for february and march?
Thanks
Let's see how this goes.
First things first, I noticed a small error in the inital Revenue Split.
Please change the 4th step in the Applied Steps for the NewOrderLines table to:
[Price] / (Duration.Days([Till] - [From] ) +1)
Need to add parentheses around the entire denominator. So that will change the split revenue to the correct split, so a a little different than your screenshot.
If you look in Power Query ( file is down below) you will see a fnCreateCalendar function. This is a small function I wrote that you enter in the start year and the end year and will produce a list of all the dates. From there, convert to a table and add whatever columns you want. Pretty clear in the file. But that produces a DimCalendar. Which I then related that DimCalendar to the NewOrderLines ( 1:M) using the Date Column from DimCalendar to Dates in the NewOrderLines. That dates column is all the dates of the split revenue.
So our data model looks like this now:
Now we will use columns from the DimCalendar table for our matrix and filters. So I'm thinking that other table you have is not really needed as it appears all that info is now coming from the DimCalendar table and filtering the NewOrderLines table.
We get this matrix, which sums the total split revenue by date by the simple formula:
Total Split Rev = SUM( NewOrderlines[SplittedRev] )
Here's the PBIX file. I also added a screenshot on how to mark the Calendar table as a date table.
10 Replies
Assuming you have a start and end date, can use power query to get these values.
1) Add a custom column with this code. it will produce a list of dates from the the From column till the Till Column:
List.Dates( [From], Duration.Days([Till] - [From] ) + 1, #duration( 1, 0,0,0) )2) add another custom column that will take the Total amount of the sale and divide it by the number of days
[Total Amount] / Duration.Days([Till] - [From] ) +1
Expand the List of dates and you will see the following:
Hi Anonymous
Thank you so much for the quick response and the great solution, it works.
Im almost done solving my problem. I have another table that is connected to my orderline table via ID.
([Maintable] Orderlines_ID to [Orderlines] ID)
My matrix has months from the maintable, how can i count the revenue from orderlines when there is already a relationship.
I thought of smoething like this but unfortunately it does not work:
CALCULATE(SUM('Orderlines'[SplittedRevenue]);USERELATIONSHIP('Orderlines'[Month];'MainTable'[Month]))Is there a way to do this for weeks instead of days?
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
