Filtering Within Date Range

[markefrody]

Hi guys, 

I’m trying to create a year/month slicer wherein it will:
1.) Display all entries wherein that year/month is included, and
2.) Will calculate the minutes and seconds for that year/monthly ONLY.

Example:
If I filter: October 2019

It will show the following results:

As you can see, if I filter for October 2019 it will show all entries wherein the month/year Oct 19 is in or in between the “StatusChangeDateFinal” (start date) and StatusDateEnd (end).

The “SegmentMinutes” and “SegmentSeconds” only computes the Oct 1, 2019 to Oct 31, 2019 range only. It will not include any dates before Oct 1, 2019 or after Oct 31, 2019.

Is there a way to filter like this and at the same time compute the minutes and seconds as date range (yr/mo) selected?

I have attached the raw data ("Raw Data" tab) and the data obtained when Oct 2019 is used ("October 2019" tab) for reference.

Sample Data 

Greatly appreciate your kind assistance. Thanks!

Best regards,
Mark V.

[v-jayw-msft · Accepted solution]

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.

[markefrody]

Hi Jay @v-jayw-msft ,

Your solution works perfectly! Thank you very much! 

Best regards,
Mark V

[v-jayw-msft]

hi @markefrody ,

Sample data is not available:

Regards,

Jay

[markefrody]

Hi Jay (@v-jayw-msft),

Thank you for your response. Please use the below link instead.
Sample Data 

Please let me know if you need further information. Really appreciate your help.

Best regards,
Mark V