2nd workday of following moth as var

[H3nning]

Hi community,

I have a little riddle I suppose. I hope anyone can solve it 🙂 Would help me a lot 😉

Ok, first of all I cannot explain all the context I have, which would be super complex, so this is a part of my problem which I hope to solve seperately. But this is for example the reason why I need the result in a variable inside a measure. So the goal is a measure with the variable:

VAR SecondWDNextMonth=?

The rest of the measure is not part of the problem, but the measure will be evaluated in context of a bar chart with month as bars. The date for the bars comes from a calendar table like this:

Date	Is Workday
2024-01-01	0
2024-01-02	1
2024-01-03	1
2024-01-04	1
...	...

 As you can see workdays are marked with 1 and weekends or holidays are marked with 0.

For every calculation of the measure I need the exact date of the second workday after the context of the measure. So when the measure is calculated for the February 2024 bar, the variable has to evaluate to 2024-03-04, because it is the second workday after Feb24 (1st is a friday, 2nd&3rd are weekend and 4th is second workday). This has to be done dynamically for each context the measure is evaluated in.

Is that even possible? I can manipulate the date table of course. But i cannot use a calculated column or something else instead. In the end I need the variable in the measure.

Thanks a lot in advance and I hope someone is up for the challange!

[Angith_Nair]

Hi @H3nning 

Yes, it’s possible to calculate the second workday of the next month dynamically within a measure using DAX in Power BI. Assuming that your calendar table (e.g., CalendarTable) has the columns Date and IsWorkday (where 1 is a workday and 0 is a non-workday):

Use the below DAX:

VAR CurrentMonth = SELECTEDVALUE(CalendarTable[Date])
VAR FirstDayNextMonth = DATE(YEAR(CurrentMonth), MONTH(CurrentMonth) + 1, 1)

VAR WorkdaysNextMonth = 
    FILTER(
        CalendarTable,
        CalendarTable[Date] >= FirstDayNextMonth &&
        CalendarTable[Date] < EOMONTH(FirstDayNextMonth, 0) + 1 &&
        CalendarTable[IsWorkday] = 1
    )

VAR SecondWDNextMonth = 
    MINX(
        TOPN(2, WorkdaysNextMonth, CalendarTable[Date], ASC),
        CalendarTable[Date]
    )

RETURN
    SecondWDNextMonth

[johnt75]

You can use

SecondWDNextMonth =
VAR WorkingDaysNextMonth =
    SELECTCOLUMNS (
        CALCULATETABLE ( 'Date', NEXTMONTH ( 'Date'[Date] ), 'Date'[Is WorkDay] = 1 ),
        'Date'[Date]
    )
VAR SecondWorkingDay =
    INDEX ( 2, WorkingDaysNextMonth, ORDERBY ( 'Date'[Date], ASC ) )
RETURN
    SecondWorkingDay

[rajendraongole1 · Accepted solution]

Hi @H3nning - Certainly! You can achieve the desired outcome using DAX by dynamically calculating the second workday after the current context's date in a measure

SecondWDNextMonth =

VAR CurrentMaxDate = MAX('DateTable'[Date])  -- Get the current context's maximum date

VAR NextWorkday =

    CALCULATE(

        MIN('DateTable'[Date]),  -- Find the next workday after the current context

        FILTER(

            'DateTable',

            'DateTable'[Date] > CurrentMaxDate &&

            WEEKDAY('DateTable'[Date], 2) <= 5  -- Check if the day is Monday to Friday (1-5)

        )

    )

VAR SecondWorkday =

    CALCULATE(

        MIN('DateTable'[Date]),  -- Find the second workday after the first workday

        FILTER(

            'DateTable',

            'DateTable'[Date] > NextWorkday &&

            WEEKDAY('DateTable'[Date], 2) <= 5  -- Again check for Monday to Friday

        )

    )

RETURN

    SecondWorkday

When you use this measure in a bar chart grouped by month, it will calculate the second workday based on the maximum date for that month dynamically.

Hope this helps.

[H3nning]

Hi, thank you for the approach. But this does not find the second workday, but the second not-weekend day after the context, does it. But it is very close. You would somehow have to take into account the coding on the date table. it will contain the information which day is a workday...

I could just replace the filter criteria for that right? I do not filter on weekday, but on the columd IsWorkday =1 instead. Ill try that!