Dynamically find out first working day of months selected in the slicer

[Dain]

Can someone please help

I have a date slicer with start and end dates.I want to find the first working day of month for each months tat comes in that slicer start n end dates.For each first working day,i want to execute a specific logic.How can i do it dynamically.

If my slicer is

4/4/2018   to    6/6/2018

I want to find out the first working day of May and June. and for these two first working days i wnat execute a specifc logic.

If my slicer is

1/4/2018    to     6/6/2018

then first working day of April,May and June. and for these 3 first working days i want to execute a specific log.

[v-shex-msft · Accepted solution]

HI @Dain,

If you mean use slicer to get dynamic calculate table, it is impossible.

You can try to use two calendar to dynamic filter first work day.(tables not contains relationship)

Steps:

1. Use calendar to create slicer, calendar2 to create table visual.

2. Write a measure to check current row contents if it suitable requirement.

First Working Day =
VAR _current =
    SELECTEDVALUE ( 'CALENDAR2'[Date] )
VAR _firstWeekDay =
    MINX (
        FILTER (
            ADDCOLUMNS (
                CALENDAR (
                    DATE ( YEAR ( _current ), MONTH ( _current ), 1 ),
                    DATE ( YEAR ( _current ), MONTH ( _current ) + 1, 1 )
                        - 1
                ),
                "Day of Week", WEEKDAY ( [Date], 1 )
            ),
            [Day of Week] <> 1
                && [Day of Week] <> 7
        ),
        [Date]
    )
RETURN
    IF (
        _current IN ALLSELECTED ( 'CALENDAR'[Date] ),
        IF ( _current = _firstWeekDay, 1, 0 ),
        0
    )

BTW, if you only want to export suitable date string based on one calendar, you can refer to below formula.

Suitable Working Day =
VAR temp =
    FILTER (
        ADDCOLUMNS (
            ALLSELECTED ( 'CALENDAR'[Date] ),
            "Year Month", FORMAT ( [Date], "mm/yyyy" )
        ),
        WEEKDAY ( 'CALENDAR'[Date], 1 ) <> 1
            && WEEKDAY ( 'CALENDAR'[Date], 1 ) <> 7
    )
RETURN
    CONCATENATEX (
        SUMMARIZE (
            temp,
            [Year Month],
            "FirstDate", MINX ( FILTER ( temp, [Year Month] = EARLIER ( [Year Month] ) ), [Date] )
        ),
        [FirstDate],
        ","
    )

Regards,
Xiaoxin Sheng