topic Re: Retrive Second Working Day in PowerBI in DAX Commands and Tips

Retrive Second Working Day in PowerBI

MONSTER123 — Wed, 22 Nov 2023 03:28:03 GMT

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!

Re: Retrive Second Working Day in PowerBI

Jihwan_Kim — Wed, 22 Nov 2023 04:11:08 GMT

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] ) ) ) )

Re: Retrive Second Working Day in PowerBI

FreemanZ — Wed, 22 Nov 2023 05:59:38 GMT

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:

Re: Retrive Second Working Day in PowerBI

Anonymous — Wed, 22 Nov 2023 10:44:51 GMT

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