no of worked days and remaining working days between choosen days

[Karthik12]

Team,

I would like to calculate no of worked days between 26th Nov 2022 till yesterday and

remaining working days from today to 31st december (Exclude Sundays and specified holidays)

Can someone please help?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I suggest having a calendar table with holiday indicating column like below.

I tried to create a sample pbix file like below.

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

No of workdays from 26Nov until yesterday: =
VAR _startdate =
    DATE ( 2022, 11, 26 )
VAR _yesterday =
    TODAY () - 1
VAR _exceptholidayandsunday =
    FILTER (
        'Calendar',
        'Calendar'[Holiday] <> 1
            && 'Calendar'[Day Name] <> "Sunday"
            && 'Calendar'[Date] >= _startdate
            && 'Calendar'[Date] <= _yesterday
    )
RETURN
    COUNTROWS ( _exceptholidayandsunday )

No of workdays from today until 31Dec: = 
VAR _startdate =
    TODAY()
VAR _enddate =
    DATE(2022,12,31)
VAR _exceptholidayandsunday =
    FILTER (
        'Calendar',
        'Calendar'[Holiday] <> 1
            && 'Calendar'[Day Name] <> "Sunday"
            && 'Calendar'[Date] >= _startdate
            && 'Calendar'[Date] <= _enddate
    )
RETURN
    COUNTROWS ( _exceptholidayandsunday )

[Karthik12]

@ddpl @Jihwan_Kim thank you both. It works fine for me.

[ddpl]

@Karthik12 try this

Working days till Yesterday = var _D = DATE(2022,11,26)
return
NETWORKDAYS(_D,TODAY()-1,11)

No of days till 31 st Dec = var _D = DATE(2022,12,31)
 return
     NETWORKDAYS(TODAY()-1,_D,11)

For holiday and other detail please go through doc: https://learn.microsoft.com/en-us/dax/networkdays-dax

[Karthik12]

@ddpl Thanks for the input. It works fine for working days till yesterday. But, remaining days was increased one day while compared to yesterday.

Actually It should be reduced one day.

Can you please check?

[ddpl · Accepted solution]

@Karthik12 sorry I forgot to remove -1

No of days till 31 st Dec = var _D = DATE(2022,12,31)
 return
     NETWORKDAYS(TODAY(),_D,11)

For holiday and other detail please go through doc: https://learn.microsoft.com/en-us/dax/networkdays-dax

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I suggest having a calendar table with holiday indicating column like below.

I tried to create a sample pbix file like below.

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

No of workdays from 26Nov until yesterday: =
VAR _startdate =
    DATE ( 2022, 11, 26 )
VAR _yesterday =
    TODAY () - 1
VAR _exceptholidayandsunday =
    FILTER (
        'Calendar',
        'Calendar'[Holiday] <> 1
            && 'Calendar'[Day Name] <> "Sunday"
            && 'Calendar'[Date] >= _startdate
            && 'Calendar'[Date] <= _yesterday
    )
RETURN
    COUNTROWS ( _exceptholidayandsunday )

No of workdays from today until 31Dec: = 
VAR _startdate =
    TODAY()
VAR _enddate =
    DATE(2022,12,31)
VAR _exceptholidayandsunday =
    FILTER (
        'Calendar',
        'Calendar'[Holiday] <> 1
            && 'Calendar'[Day Name] <> "Sunday"
            && 'Calendar'[Date] >= _startdate
            && 'Calendar'[Date] <= _enddate
    )
RETURN
    COUNTROWS ( _exceptholidayandsunday )