Cumulative timeline using average days from differents phases

[romovaro]

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.

[fahadqadir3]

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

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]

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

[romovaro]

HI fahadqadir3 

That's what I need.

Created a table and also the measure like you said.

but my table comes blank.

Did you use diff Phase formula? Tables need to be connected?

Can you share pbix just in case?

Phase1_Days - Contract2Green =

CALCULATE(

    AVERAGEX(

        FY24,

        DATEDIFF(FY24[Contrat Signature date], FY24[Greenlight Date], DAY)

    ),

    FY24[Contrat Signature date] >= DATE(2023, 7, 1) && FY24[Contrat Signature date] < DATE(2024, 6, 30)

)

Thanks

[fahadqadir3 · Accepted solution]

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.

[romovaro]

HI

I have formulas for every phase as below:

Phase1_Days - Contract2Green =

CALCULATE(

    AVERAGEX(

        FY24,

        DATEDIFF(FY24[Contrat Signature date], FY24[Greenlight Date], DAY)

    ),

    FY24[Contrat Signature date] >= DATE(2023, 7, 1) && FY24[Contrat Signature date] < DATE(2024, 6, 30)

)

Phase2_Days - Green2Sales =

CALCULATE(

    AVERAGEX(

        FY24,

        DATEDIFF(FY24[Greenlight Date], FY24[Sales Handover date], DAY)

    ),

    FY24[Greenlight Date] >= DATE(2023, 7, 1) && FY24[Greenlight Date] < DATE(2024, 6, 30)

)

Phase3_Days - Sales21stCall =

CALCULATE(

    AVERAGEX(

        FY24,

        DATEDIFF(FY24[Sales Handover date], FY24[1st Engagement Call Date], DAY)

    ),

    FY24[Sales Handover date] >= DATE(2023, 7, 1) && FY24[Sales Handover date] < DATE(2024, 6, 30)

)

... etc until last phase:

Phase6_Days - PMAllocation2Handover =

CALCULATE(

    AVERAGEX(

        FY24,

        DATEDIFF(FY24[PM Allocation Date], FY24[Handover to PM Date], DAY)

    ),

    FY24[PM Allocation Date] >= DATE(2023, 7, 1) && FY24[PM Allocation Date] < DATE(2024, 6, 30)

)

 then trying to create a cumulative line whit the formula below but no t really showing what I need. (see Excel screenshot previous post). 

Phases_Cumulative =

CALCULATE(

    [Phase1_Days - Contract2Green] + [Phase2_Days - Green2Sales] + [Phase3_Days - Sales21stCall] + [Phase4_Days - 1stCall2ReadyPMAllocation] + [Phase5_Days - 1stCall2ReadyPMAllocation] + [Phase6_Days - PMAllocation2Handover],

    FILTER(

        ALLSELECTED(FY24[Contrat Signature date]),

        FY24[Contrat Signature date] <= MAX(FY24[Contrat Signature date])

))

thanks

@jgeddes @fahadqadir3