Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
konradjonsson
Helper II
Helper II

How to get networkdays for previous period?

Hi.

I have created a networkdays measure that gives me the correct number of net working days, per country. This is the measure 'Workdays - Net' below.

 

The challenge I have is to get net working days, per country, for the same period last year. It seems like the measure I am using for this (Workdays PY - Net) is not considering the Holiday dates in the Global workdays-table.

Workdays - Gross PY.png

 

 

Any ideas what I am doing wrong?

 

 

Workdays - Net =

VAR HolidayDates =
    CALCULATETABLE (
        DISTINCT ( 'Global workdays'[Date]),
            'Global workdays'[IsHoliday] = 1
                )

VAR WorkingDays =
                NETWORKDAYS (
                    STARTOFMONTH ( 'Date - Main'[WKDate] ),
                    ENDOFMONTH ( 'Date - Main'[WKDate] ),
                    1,
                    HolidayDates
                            )
RETURN
WorkingDays
 
 
Workdays - Net PY =

VAR HolidayDates =
    CALCULATETABLE (
        DISTINCT ( 'Global workdays'[Date]),
            'Global workdays'[IsHoliday] = 1
                )

VAR WorkingDays =
NETWORKDAYS (
    STARTOFMONTH (DATEADD( 'Date - Main'[WKDate],-1,YEAR) ),
    ENDOFMONTH ( DATEADD('Date - Main'[WKDate],-1,YEAR) ),
    1,
    HolidayDates
)
RETURN
WorkingDays
 
1 ACCEPTED SOLUTION

HI @konradjonsson,

So you mean these calculations also need to be filtered with the current region? If that is the case, does any fields in your holiday table can be used to distinguish these records? 

If that is the case, you can try to modify the holiday variable to add filter with regions to get accurate holiday date list to use in formula calculations.

For the total level calculation, you can try to use summarize function to create a temp table to summary detail level records and calculation correspond result, then you can use iterator function sumx to summary the temp table records.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
Ahmedx
Super User
Super User

try creating a column in the calendar table

NETWORKDAYS = NETWORKDAYS('Date'[Date],[Date],1,VALUES(Holiday[HolidayDates]))

https://dropmefiles.com/KVxO2
far everything is simple, all that 1 is a working day

Measure = SUM('Date'[NETWORKDAYS])

 

 

v-shex-msft
Community Support
Community Support

Hi @konradjonsson,

It seems like NETWORKDAYS function not suitable to nest with other date functions, perhaps you can try to use date functions(CALENDAR) to reproduce these calculations:

Workdays - Net PY =
VAR HolidayDates =
    CALCULATETABLE (
        DISTINCT ( 'Global workdays'[Date] ),
        FILTER ( ALLSELECTED ( 'Global workdays' ), 'Global workdays'[IsHoliday] = 1 )
    )
VAR _stDate =
    MIN ( 'Date - Main'[WKDate] )
VAR _edDate =
    MAX ( 'Date - Main'[WKDate] )
VAR WorkingDays =
    COUNTROWS (
        FILTER (
            CALENDAR (
                DATE ( YEAR ( _stDate ) - 1, MONTH ( _stDate ), DAY ( _stDate ) ),
                DATE ( YEAR ( _edDate ) - 1, MONTH ( _edDate ), DAY ( _edDate ) )
            ),
            WEEKDAY ( [Date], 2 ) <= 5
                && NOT ( [Date] IN HolidayDates )
        )
    )
RETURN
    WorkingDays

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin.

Thank you for your suggestion. I did try it, but it does not work.

The issues with this suggestion are twofold:

1)it reduces the net work days to the minimum across all selected countries (the more countries I add to the list, the lower the net work days becomes).

2)if I include the PY period (2112) in the visual, I get a different result from when I only have 2212 in the visual (should not be dependent on that filter).

 

Net work days are the same for all countries. The more countries being added, the lower the result becomes.

Workdays - Net PY 3, 2212 only.png

Net work days changes to 19 when period filter include 2112 & 2212

Workdays - Net PY 3, 2112 & 2212.png

HI @konradjonsson,

So you mean these calculations also need to be filtered with the current region? If that is the case, does any fields in your holiday table can be used to distinguish these records? 

If that is the case, you can try to modify the holiday variable to add filter with regions to get accurate holiday date list to use in formula calculations.

For the total level calculation, you can try to use summarize function to create a temp table to summary detail level records and calculation correspond result, then you can use iterator function sumx to summary the temp table records.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@konradjonsson , Try like

 

VAR WorkingDays =
NETWORKDAYS (
(Date( year('Date - Main'[WKDate])-1,month('Date - Main'[WKDate]),1) ),
EOMonth (Date( year('Date - Main'[WKDate])-1,month('Date - Main'[WKDate]),1),0 ),
1,
HolidayDates
)

 

In case you need same weekdays, then just subract 364 number from date

 

like

 

VAR WorkingDays =
NETWORKDAYS (
eomonth ( 'Date - Main'[WKDate],-1 )+1 -364,
eomonth ( 'Date - Main'[WKDate] ,0) -364,
1,
HolidayDates
)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thanks Amit.

When I try to replace the DATEADD-function with "Date(year...", the date field (WKDate) is no longer recognized; "Cannot find name WKDate".

 

I was not clear in specifying that, in the included snapshot, the period is filtered to 2212 (i.e. Dec 2022). To me, it seems like the DAX measure cannot find the holiday dates of 2112 (Dec 2021).

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.