Calculate the number of skipped intervals, planned vs completed

[Anonymous]

Hi, I am trying to create a measure that would count the number of intervals that were skipped on a project.  A project can have x amount of sub projects.  Each sub project has x amounts of planned intervals.  Once there is completed data, it is submitted to another table.  If an interval is not completed, no data is submitted.  Here is an example of the data:

Planned data

ProjectIDID	SubProjectID	IntervalNumber	ProgToBeTreated
1112	2961	7	Y
1112	2961	6	Y
1112	2961	5	Y
1112	2961	4	Y
1112	2961	3	Y
1112	2961	2	Y
1112	2961	1	Y
1112	2960	5	Y
1112	2960	4	Y
1112	2960	3	Y
1112	2960	2	Y
1112	2960	1	Y
1112	2959	4	Y
1112	2959	3	Y
1112	2959	2	Y
1112	2959	1	Y

active/completed data

ProjectIDID	SubProjectID	IntervalNumber
1112	2961	4
1112	2961	2
1112	2961	1
1112	2960	3
1112	2960	2
1112	2960	1
1112	2959	4
1112	2959	2
1112	2959	1

2961 and 2959 have skipped interval 3 and moved directly onto 4.  I am trying to create a measure that would record those 2 skipped intervals without data.  The following formula works when the job is completed, but I would like to have an active count.

SkippedIntervals = calculate(COUNT(PlannedTable[Intervalnumber]),PlannedTable[ProgToBeTreated]="Y")-CALCULATE(COUNT(CompletedTable[IntervalNumber]))

I tried to add a max interval variable, but it is not dynamic when 2961 has a max of 4, but 2960 has a max of 3

SkippedIntervals =

var MAXInterval = calculate(MAX(CompletedTable[IntervalNumber]))

return

calculate(COUNT(PlannedTable[Intervalnumber]),PlannedTable[ProgToBeTreated]="Y",PlannedTable[IntervalNumber]<=MAXInterval)-CALCULATE(COUNT(CompletedTable[IntervalNumber]))

Any help is appreciated

[mahoneypat]

Here is one measure expression that should work.

SkippedIntervals =
VAR vSummary =
    ADDCOLUMNS (
        SUMMARIZE ( Projects, Projects[ProjectIDID], Projects[SubProjectID] ),
        "@Max", CALCULATE ( MAX ( Projects[IntervalNumber] ) ),
        "@Intervals", CALCULATE ( COUNTROWS ( Projects ) )
    )
RETURN
    SUMX ( vSummary, [@Max] - [@Intervals] )

Pat

[Anonymous]

With some additional testing, the solution I put forward is incorrect.  Looking for additional help :C

[Anonymous]

Would this be an acceptable solution?  I don't have the dataset to 100% test this yet to see if it would be dynamic for each SubProjectID.  Just building off your solution here:

SkippedIntervalsNew =

VAR vSummary =

ADDCOLUMNS (

SUMMARIZE ( Completed, Completed[STEPProgrammedPadID], Completed[STEPProgrammedWellID] ),

"@Max", CALCULATE(MAX ( Completed[IntervalNumber] )) ,

"@Intervals", CALCULATE ( COUNTROWS ( Completed) ),

"@PlannedSkips", CALCULATE( COUNTROWS (Planned), Planned[ProgToBeTreated]="N",FILTER(Planned,Planned[IntervalNumber]<MAX ( Completed[IntervalNumber] )))

)

RETURN

SUMX ( vSummary, [@Max] - [@Intervals] -[@PlannedSkips])

[Anonymous]

Thanks so much for quick response!  This solves about 90% solutions.  I have one quick question that I hope is an easy answer.  In the PlannedData, there is a ProgToBeTreated column.  Sometimes it is planned to skip treating an interval.  How would I go about subtracting the intervals that were planned to be skipped, once @ max has reached/past those intervals?  Thanks!