Forum Discussion
Helper IDynamic revenue Data +4 weeks and - weeks
Hi lsuresh89
Please see the attached file with the solution, this file contains three tables:
- YourSample - the sample that was provided.
- ExpandedDays - a table that extends YourSample table to individual day between "Agreement Start Date" and "Agreement End Date" and splits Total Revenue to individual Date.
- Relative Date Dimension table that enables filtering the weeks to current, previous 4 and forthcoming 4, here is the blog about it if you are interested https://community.powerbi.com/t5/Community-Blog/Relative-Date-Dimension/ba-p/779039
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.Hi lsuresh89
To clear the error adjust the ExpandDays Function by adding +1 at the end of days step like below.
If adding +1 didn't fix the issue, then you need to check the data set if the start date is always lower or equal to end date as the error suggest this is not the case.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Helper IHi Mariusz ,
Thanks for the prompt solution. I implemented the same and it does work. However, there might be something wrong with what I'm doing because the weekly revenue numbers do not get split correctly. The numbers are flat across all weeks.
Also want to know how I can get correct values when I put the total revenue numbers in a matrix, right now it's getting multiplied by 63 for some reason. I have attached the pbix file for your reference. Thanks in advance.
Regards,
Lakshmi
Helper IMariusz Also wanted to add that I'm getting this error: 'OLE DB or ODBC error: [Expression.Error] The 'increment' argument is out of range.'
Any suggestions? I have a big dataset of thousands of rows. Any way I can still use your solution with some tweaks?
- Mariusz
Community Champion
Hi lsuresh89
To clear the error adjust the ExpandDays Function by adding +1 at the end of days step like below.
If adding +1 didn't fix the issue, then you need to check the data set if the start date is always lower or equal to end date as the error suggest this is not the case.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.- lsuresh89
Hi Mariusz , you are correct. There are several blank start dates in my final dataset. That is why the error when subtracting end and start date. Is there a code that can handle blank start dates as well?
One more question. How do I attach the pbix file here? I cant seem to find the icon :)
- Mariusz
Hi lsuresh89
It is common to see blank end dates replaced with today's date, and that is done currently in the function, my question would be what do you want to do with blank start dates?
You can share your files through Dropbox, Google Drive or One Drive
Best Regards,
Mariusz
Please feel free to connect with me.