Retrive Second Working Day in PowerBI

[MONSTER123]

Hi, 

I have imported all my daily reports into my powerbi.

I would like to have one dashboard with all the reports that dated every 2nd weekday of the month.

How can I create a formula to retrieve all the 2nd weekday of the month.

In Excel formula : =WORKDAY(DATE(YEAR(A1),MONTH(A1),1)-1,2)

For example:

I need to get the report that is dated dated 02.11.2023 for Nov 2023 and 04.12.2023 for Dec 2023,

Thank You for your kind assistance!

[Anonymous]

First you have to  add new column and write below dax function, It will work for your requirement.

HOLLIDAY =

VAR secweek_num = IF( ROUNDUP(DIVIDE(DAY('calendar'[Date]), 7), 0) IN {2,4} && WEEKDAY('calendar'[Date]) IN{7,1},"LEAVE", "WORKING DAY")

VAR firweek_num = IF( WEEKDAY('calendar'[Date]) = 1, "LEAVE", secweek_num)

RETURN

firweek_num

Here below i have attached my screen shoot

[FreemanZ]

hi @MONSTER123 ,

not sure if i fully get you, supposing your dates table is like:

dates = CALENDAR(DATE(2023, 11, 1), DATE(2024, 2, 5))

try to add a calculated column like below:

2ndWD = 
VAR _currentdate = [date]
VAR _workdaysinmonth= 
FILTER(
    dates,
    MONTH(dates[date]) = MONTH(_currentdate)
        &&YEAR(dates[date]) = YEAR(_currentdate)
        &&NOT WEEKDAY(dates[date], 2) IN  {6, 7}
)
VAR _firstworkday = MINX(TOPN(1, _workdaysinmonth, [date], ASC), [date])
VAR _secondworkday = MINX(FILTER(_workdaysinmonth, [date]>_firstworkday), [date])
VAR _result = IF(_currentdate = _secondworkday, "yes", "no")
RETURN _result

it worked like:

[Jihwan_Kim]

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your semantic model.

INDEX function (DAX) - DAX | Microsoft Learn

Expected result measure: =
VAR _t =
    FILTER (
        SUMMARIZE (
            ALL ( Sales ),
            'Calendar'[Date],
            'Calendar'[Weekday name sort],
            'Calendar'[Year-Month sort]
        ),
        NOT ( 'Calendar'[Weekday name sort] IN { 1, 7 } )
    )
RETURN
    CALCULATE (
        SUM ( Sales[Quantity] ),
        KEEPFILTERS (
            INDEX (
                2,
                _t,
                ORDERBY ( 'Calendar'[Date], ASC ),
                ,
                PARTITIONBY ( 'Calendar'[Year-Month sort] )
            )
        )
    )