Filtering Within Date Range

Hi @markefrody,

Please check following steps and see if the result achieve your expectation:

1. Create calculate table as slicer:

Table 2 =

UNION (

    SELECTCOLUMNS (

        'Table',

        "yearmonth", FORMAT ( 'Table'[StatusChangeDateFinal ], "YYYYMM" )

    ),

    SELECTCOLUMNS (

        'Table',

        "yearmonth", FORMAT ( 'Table'[StatusDateEnd], "YYYYMM" )

    )

)

2. Create measures:

SegmentMinutes =

VAR sv =

    SELECTEDVALUE ( 'Table 2'[yearmonth] )

VAR monthstart =

    DATE ( MID ( sv, 1, 4 ), MID ( sv, 5, 2 ), "01" )

VAR monthend =

    EDATE ( monthstart, 1 )

VAR finalym =

    FORMAT ( MAX ( 'Table'[StatusChangeDateFinal ] ), "YYYYMM" )

VAR endym =

    FORMAT ( MAX ( 'Table'[StatusDateEnd] ), "YYYYMM" )

VAR diff1 =

    DATEDIFF (

        MAX ( 'Table'[StatusChangeDateFinal ] ),

        MAX ( 'Table'[StatusDateEnd] ),

        MINUTE

    )

VAR diff2 =

    DATEDIFF ( monthstart, monthend, MINUTE )

VAR diff3 =

    DATEDIFF ( monthstart, MAX ( 'Table'[StatusDateEnd] ), MINUTE )

VAR diff4 =

    DATEDIFF ( MAX ( 'Table'[StatusChangeDateFinal ] ), monthend, MINUTE )

RETURN

    IF (

        NOT ( ISFILTERED ( 'Table 2'[yearmonth] ) ),

        0,

        IF (

            finalym = sv

                && endym = sv,

            diff1,

            IF (

                finalym < sv

                    && endym > sv,

                diff2,

                IF (

                    finalym < sv

                        && endym = sv,

                    diff3,

                    IF ( finalym = sv && endym > sv, diff4 )

                )

            )

        )

)
And for SegmentSeconds we just need to replace “DATEDIFF(XX,YY,MINUTE)” to “DATEDIFF(XX,YY,SECOND)”.

3. Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.