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.
Hard for me to see that in my mind, any chance you can a sample pbix/excel file that has what you described? Dropbox/One Drive works great.
But initially, always better to have a dedicated Calendar table and use that to filter your fact table.
Hi Anonymous
Its so hard to recreate this file, i hope this example file is good enough...
In the pbix there is a screenshot of my excel with the sum for january that should appear.
The use of a dedicated DataTable is such a great advice, could you tell me more about it?
Should i connect every other table to that datetable? (Thats not possible without adding inactive relations, right?)
https://www.dropbox.com/s/0qzcvvsp261tuvc/example_splitRev.pbix?dl=0
Thanks
Anonymous
I will take a look at the file and see what I can come up with.
Regarding the date table. It's usually a good idea to have a dedicated date table. If you dont have one you can create one in DAX ( like you have in your file) or in Power Query. I prefer power query as you can add other columns you might want ( i.e. month name or month number) via the user interface pretty easily. You also need a date table to make sure the buit-in time intelligence functions work correctly. Just be sure to set your date table as a date table in the Date View--> Modeling--> Calendars--> Mark as Date table. Just be sure that you have all the days of the year.
You will relate this to your fact table(s) and then as a 1 to many relationship. Then you would use the columns the date table as filter, be it on rows/columns or slicers/filters. I don't want to get to much into the date table, like how to build a flexible one or all the ins-and-outs in this post. So let's figure out a solution to your last problem and then we can tackle the date table if you want .
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.
Thank you so much.
Thanks for the clear and good explanations.
The seperation per Month looks fine to me.
You mentioned my other Table (newMain) and that it looks unnecessary. My problem is that in my "problem-report" this is the actual main table, and all the diagrams are based of the month column out of this table.
I'll try this logic now and let you know if it works in my setup.
Otherwise i should think about changing the whole report to a dimCalendar base...