Dynamic revenue Data +4 weeks and - weeks

[lsuresh89]

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.

[Mariusz · Accepted solution]

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.

[Mariusz · Accepted solution]

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.

[lsuresh89]

Hi @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

[Mariusz]

Hi @lsuresh89 

The solution I've provided should divide Total Revenue by the number of days between Agreement Start Date and Agreement End Date, this is done in "Added Custom" step of ExpandedDays Query,  please can you attach the file as I can not see it.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[lsuresh89]

@Mariusz  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 · Accepted solution]

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.

[lsuresh89]

hi @Mariusz ,

Thanks so much for all the help. The solution is amazing and is much better than how I did it in Excel.

[lsuresh89]

hi @Mariusz , if I have to plug in the actuals against the pipeline, like in the image above (also by week, the same shared axis) then how do I do that. 

Regards,

Lakshmi

[Mariusz]

Hi @lsuresh89 

Please see the below example for Revenue, you can replicate this step for any other value in this table by adding the extra column.
All it does is dividing the original number by a number of days between start and end dates

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 , The only problem with using this step is that for actuals I do not want to divide the total profit by week. I have the weekly numbers with me. How can i tweak this formula and the ExpandDays function?

I have the weekly profitability numbers by produtc viz. Week 36 - $ 4567, Week 35 - $ 5657.... but I cant find a way to connect these weekly numbers with my pipleine.

[Mariusz]

Hi @lsuresh89 

Is pipeline on a weekly level of granularity?

If so you can use a date dimension contaning weeks and dates and create relationships accordingly.

Best Regards,
Mariusz

Please feel free to connect with me.

[lsuresh89]

Hi @Mariusz, unfortunately pipeline isnt at week level. My biggest challenge is getting the actuals and pipeline to share the same axis. That's where I am super stuck. I tried creating relationships but there are multiple columns even for that....dint quite work.