Forum Discussion

lsuresh89's avatar
lsuresh89
Icon for Helper I rankHelper I
7 years ago
Solved

Dynamic 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) nu...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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 Repczynski

     

  • Mariusz's avatar
    Mariusz
    7 years ago

    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.
    Mariusz Repczynski

     

lsuresh89's avatar
lsuresh89
Icon for Helper I rankHelper I
7 years ago

 

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's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

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.
Mariusz Repczynski

 

  • lsuresh89's avatar
    lsuresh89
    Icon for Helper I rankHelper I
    7 years ago

    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's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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.
    Mariusz Repczynski

     

  • lsuresh89's avatar
    lsuresh89
    Icon for Helper I rankHelper I
    7 years ago

    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.