Modify filter context in CALCULATE by a condition that checks previous row value in a group

[anonymous3]

Hi community

I have the following dataset:

There is the measure _IsRepeatedEvent that creates binary column based on some logic. Also I have a slicer with StartDate.[Year] values (I will refer to it as the SelectedYear below.).

Let SelectedYear be 2019. What I need to do is create a new measure that distinctly counts Group column only where: _IsRepeatedEvent == 1 && StartDate >= SelectedYear (blue dates on the img above) && previous/lag/earlier EndDate == SelectedYear (yellow dates on the img above). So the result in the example above for a measure has to be equal 2 (distinct count of (b, b, c, c))

I came up with the following measure:

_Measure = 
VAR _SelectedYear =
    MIN ( table[StartDate].[Year] )

VAR Result = 
    CALCULATE(
        DISTINCTCOUNT(table[Group]), 
        FILTER(
            ALL( table ),
            [_IsRepeatedEvent] == 1 &&
            reoffending[StartDate].[Year] >= _SelectedYear     
        )
    )
Return Result

I'm really struggling in figuring out how to modify filter to check previous EndDate year. I'll be appreciated for any help.

[johnt75]

Try

Num groups =
VAR _SelectedYear =
    MIN ( table[StartDate].[Year] )
VAR InitialTable =
    FILTER (
        'Table',
        [_IsRepeatedEvent] = 1
            && 'Table'[Start Date].[Year] = _SelectedYear
    )
VAR SummaryTable =
    ADDCOLUMNS (
        InitialTable,
        "Prior End Date",
            VAR CurrentID = 'Table'[ID]
            RETURN
                MAXX (
                    CALCULATETABLE (
                        TOPN ( 1, 'Table', 'Table'[ID] ),
                        ALLEXCEPT ( 'Table', 'Table'[Group] ),
                        'Table'[ID] < CurrentID
                    ),
                    'Table'[End date]
                )
    )
RETURN
    COUNTROWS (
        DISTINCT ( FILTER ( SummaryTable, YEAR ( [Prior End Date] ) = _SelectedYear ) )
    )

[anonymous3]

@johnt75 It gives me (Blank) for 2019 and 1 when selected all

Thanks for quick reply 

[johnt75 · Accepted solution]

I think its the selected year calculation

Try

Num groups =
VAR _SelectedYears =
    VALUES ( 'Table'[Start Date].[Year] )
VAR InitialTable =
    FILTER (
        'Table',
        'Table'[Is Repeated] = 1
            && 'Table'[Start Date].[Year] IN _SelectedYears
    )
VAR SummaryTable =
    ADDCOLUMNS (
        InitialTable,
        "Prior End Date",
            VAR CurrentID = 'Table'[ID]
            RETURN
                MAXX (
                    CALCULATETABLE (
                        TOPN ( 1, 'Table', 'Table'[ID] ),
                        ALLEXCEPT ( 'Table', 'Table'[Group] ),
                        'Table'[ID] < CurrentID
                    ),
                    'Table'[End date]
                )
    )
RETURN
    COUNTROWS (
        DISTINCT ( FILTER ( SummaryTable, YEAR ( [Prior End Date] ) IN _SelectedYears ) )
    )

[anonymous3]

It takes pretty long time to calculate though

[anonymous3]

It works, thank you!

[Greg_Deckler]

@anonymous3 Maybe try this approach See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous