Forum Discussion

philjohn's avatar
philjohn
Frequent Visitor
5 months ago
Solved

P6 Metrics using several date fields in one table

I am trying to replicate a table from excel in Power BI with a P6 export
Year/Month - followed by several metrics including "In Period Activities Started as Planned" "In Period Activities Finished not in Baseline"

To break down these metrics I need to use
In Period Activities Started as Planned (Year/Month match with Actual Start (date) and match with Baseline Start (date), countrows)
In Period Activities Finished not in Baseline (Year/Month match with Actual Finish (date) and doesn't match with Baseline Finish (date), countrows)

I made a date table and connected it relationally to all the dates, I've tried using "use relationship" "treatas" "filter"
And it just keeps calculating incorrectly - is my structure incorrect? is this even possible to get all these metrics to sit aside one date table column - its so easy in excel 

Need Help
  • johnt75's avatar
    johnt75
    5 months ago

    I would create inactive relationships with the actual start and end date columns but not the baseline columns. You can manually apply filters to the baseline columns like

    In Period Activities Started as Planned =
VAR VisibleDates =
    VALUES ( 'Date'[Date] )
VAR Result =
    COUNTROWS (
        CALCULATETABLE (
            Activities,
            USERELATIONSHIP ( 'Date'[Date], Activities[Actual Start Date] ),
            Activities[Baseline Start Date] IN VisibleDates
        )
    )
RETURN
    Result

In Period Activities Finished not in Baseline =
VAR VisibleDates =
    VALUES ( 'Date'[Date] )
VAR Result =
    COUNTROWS (
        CALCULATETABLE (
            Activities,
            USERELATIONSHIP ( 'Date'[Date], Activities[Actual Finish Date] ),
            NOT Activities[Baseline Finish Date] IN VisibleDates
        )
    )
RETURN
    Result
  • philjohn's avatar
    philjohn
    5 months ago

    It worked!!! Oh god its been 2 days of me smashing my head against the desk, thank you so so so much

5 Replies

  • johnt75's avatar
    johnt75
    Super User
    5 months ago

    I would create inactive relationships with the actual start and end date columns but not the baseline columns. You can manually apply filters to the baseline columns like

    In Period Activities Started as Planned =
VAR VisibleDates =
    VALUES ( 'Date'[Date] )
VAR Result =
    COUNTROWS (
        CALCULATETABLE (
            Activities,
            USERELATIONSHIP ( 'Date'[Date], Activities[Actual Start Date] ),
            Activities[Baseline Start Date] IN VisibleDates
        )
    )
RETURN
    Result

In Period Activities Finished not in Baseline =
VAR VisibleDates =
    VALUES ( 'Date'[Date] )
VAR Result =
    COUNTROWS (
        CALCULATETABLE (
            Activities,
            USERELATIONSHIP ( 'Date'[Date], Activities[Actual Finish Date] ),
            NOT Activities[Baseline Finish Date] IN VisibleDates
        )
    )
RETURN
    Result
    • philjohn's avatar
      philjohn
      Frequent Visitor
      5 months ago

      It worked!!! Oh god its been 2 days of me smashing my head against the desk, thank you so so so much

    • philjohn's avatar
      philjohn
      Frequent Visitor
      5 months ago

      johnt75  sorry to bother you again, I now need to make the same table but rather than in month count, its cumulative? 

      • johnt75's avatar
        johnt75
        Super User
        5 months ago

        If you want to show cumulative for the current year, I think you can use

        In Period Activities Started as Planned =
VAR VisibleDates =
    DATESYTD ( 'Date'[Date] )
VAR Result =
    COUNTROWS (
        CALCULATETABLE (
            Activities,
            USERELATIONSHIP ( 'Date'[Date], Activities[Actual Start Date] ),
            VisibleDates,
            Activities[Baseline Start Date] IN VisibleDates
        )
    )
RETURN
    Result

In Period Activities Finished not in Baseline =
VAR VisibleDates =
    DATESYTD ( 'Date'[Date] )
VAR Result =
    COUNTROWS (
        CALCULATETABLE (
            Activities,
            USERELATIONSHIP ( 'Date'[Date], Activities[Actual Finish Date] ),
            VisibleDates,
            NOT Activities[Baseline Finish Date] IN VisibleDates
        )
    )
RETURN
    Result
  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    5 months ago

    philjohn 

     

    Hey, ditch the multiple date relationships - that's your problem.

    In Period Started Planned =
    CALCULATE(
    COUNTROWS(P6),
    YEAR(P6[Actual Start]) = MAX(Date[Year]),
    MONTH(P6[Actual Start]) = MAX(Date[Month]),
    P6[Actual Start] = P6[Baseline Start]
    )

     

    In Period Finished Not Baseline =
    CALCULATE(
    COUNTROWS(P6),
    YEAR(P6[Actual Finish]) = MAX(Date[Year]),
    MONTH(P6[Actual Finish]) = MAX(Date[Month]),
    P6[Actual Finish] <> P6[Baseline Finish]
    )