Forum Discussion

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive Resident
2 years ago
Solved

Cumulative timeline using average days from differents phases

Hello

 

I have a question regarding creating a cumulative timeline.

I have a table with different phases and dates

 

 

I can create graphs using datediff to see the average of Days between phases.

 

 

I have been asked to create a cumulative timeline or at, least a cumulative graph to see overall the time to engage by segment and identify the steps where we spend more time. I am looking for a sort of cumulative timeline that shows the # of days for each step in the process.  

 

I have checked different ways in the forum but this time there are multiple dates and formulas are not really working.

ANy help is welcome, thanks.

 

 

 

Need Help
  • fahadqadir3's avatar
    fahadqadir3
    2 years ago

    PBI.pbix 

    No need to create relationship between tables.

    Power BI file attached, In case you're not able to open this workbook, you need to use latest version of Power BI desktop to use this workbook.

     

    Did I answer your question? Then please mark my post as the solution.
    If I helped you, click on the Thumbs Up to give Kudos.

6 Replies

  • fahadqadir3's avatar
    fahadqadir3
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    romovaro In this Case You first need to Unpivot Your Date Columns, Please review the following screenshot and attached pbix file.

    You want to present total (cummulative) days for each segment or by each Index in my case or whatever dimension you have.

     

    Power BI File attached

    sad.pbix

    SalesHandOverDate = 
    CALCULATE(
        SUM('Updated Table'[Date]),
        FILTER('Updated Table', 'Updated Table'[Attribute] = "SalesHandOverDate")
    )


    1st Engagement Call Date = CALCULATE(
        SUM('Updated Table'[Date]),
        FILTER('Updated Table', 'Updated Table'[Attribute] = "1st Engagement Call Date")
    )

    SalesHandOvertoFirstEngange = DATEDIFF([SalesHandOverDate],[1st Engagement Call Date],DAY)

    Did I answer your question? Then please mark my post as the solution.
    If I helped you, click on the Thumbs Up to give Kudos.

    • romovaro's avatar
      romovaro
      Icon for Responsive Resident rankResponsive Resident
      2 years ago

      Thanks fahadqadir3,

       

      I have problems seeign your dasboard. I cannot see anything.

       

      I want to show the average by days x phase so I can provide timelines graphs like the one below:

      The idea is to:

       

      SUM (Av signature vs Greenlight) + SUM (Av GreenL vs Sales handover)........ it shows a trend of total days by phase.

       

       

       

       

      x

       

       

       

      • fahadqadir3's avatar
        fahadqadir3
        Icon for Solution Supplier rankSolution Supplier
        2 years ago

        romovaro  You first need to create a new table like this:

        Then create  a following measure

        Cummulative Line = SWITCH(MAX('COLUMNS'[NAME]),
        "Sign vs GL",[AVG Signature vs GL],
        "GL vs Sales HO",[AVG Green Light vs Sales HO]+[AVG Signature vs GL],
        "Sales HO vs 1stEngangeCall",
        [AVG Green Light vs Sales HO]+[AVG Signature vs GL]+[AVG SalesHO vs 1st Engangement Call])


         

        Did I answer your question? Then please mark my post as the solution.
        If I helped you, click on the Thumbs Up to give Kudos.