Forum Discussion
Split Revenue per Month
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.
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:
Is there a way to do this for weeks instead of days?