Forum Discussion
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 n...
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 ResultIt worked!!! Oh god its been 2 days of me smashing my head against the desk, thank you so so so much
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
johnt75 sorry to bother you again, I now need to make the same table but rather than in month count, its cumulative?
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