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

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

 

 

 

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive Resident
2 years ago
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