Forum Discussion
Helper IDynamic revenue Data +4 weeks and - weeks
Dear All,
I have a challenging requirement that I have managed to solve using excel but would rather have it done more efficiently in Power BI.
I have a set of data with pipeline (revenue) numbers for different contracts by product. These contracts have a start and end date(some dont have the end date). The revenue numbers are for the entire contract period. The requirement is to show the pipeline for the last 4 weeks, current week and next 4 weeks in the form of a bar chart. This is to see the pipeline growth/de-growth.
| Product | Total Revenue | Agreement Start Date | Agreement End Date | Current Week | C-1 | C-2 | C-3 | C-4 | C+1 | C+2 | C+3 | C+4 |
| A | 20000 | 26-01-2019 | 28-12-2019 | |||||||||
| B | 54000 | 26-01-2019 | 28-12-2019 | |||||||||
| C | 78997 | 31-01-2019 | 31-03-2019 | |||||||||
| A | 343342 | 26-01-2019 | 28-12-2019 | |||||||||
| B | 270000 | 26-01-2019 | 31-12-2019 | |||||||||
| A | 10000 | 30-01-2019 | 31-12-2019 | |||||||||
| C | 96000 | 27-01-2019 | 31-12-2019 |
Where I'm stuck is that in excel I managed to get the last 4 weeks and next 4 weeks pipeline but I had to create 9 columns to achieve this. Is there a better way to do this in Power BI by dynamically creating columns that will help calculate the c urrent week and last 4 /next 4 weeks data?
Looking forward to your expert solution!Thanks.
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.
13 Replies
- Mariusz
Community Champion
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.
lsuresh89Hi 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