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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
asmaG
Frequent Visitor

calculate number of days between 2 dates except weekend and free days

hello ,  i'm working with power bi RS and
i'm trying to calculate number of days between startdate and enddate except the week end and free day but i haven't found

any idea please

thank you

1 ACCEPTED SOLUTION

hello , i finally found a solution

here the dax function that i used after creating a date table that containt holidays days

 

test_wee = RoundDown(DateDiff(t_faits[DATE_1], t_faits[DATE_2], Day) / 7, 0) * 5 +
Mod(5 + Weekday(t_faits[DATE_2]) - Weekday(t_faits[DATE_1]), 5)- calculate ( COUNT ( calendrier[Date] ), FILTER ( calendrier,t_faits[DATE_1] <= calendrier[Date]
&& t_faits[DATE_2] >= calendrier[Date]
&& ( calendrier[isholiday] = 1)))

View solution in original post

8 REPLIES 8
josef78
Memorable Member
Memorable Member

Try NETWORKDAYS function

asmaG
Frequent Visitor

i tried it but it did'nt accept it it's not a regonized function i'm work with power bi rs Version: 2.105.1143.0 64-bit (mai 2022)

josef78
Memorable Member
Memorable Member

It is relatively new function, you must upgrade least to September 2022 (or bettter to last version).

Power BI Report Server September 2022 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI

asmaG
Frequent Visitor

i don't have the permission to made it because i work with client computer and it's gonna take a long long time to have those permission 😕

josef78
Memorable Member
Memorable Member

Try ask our client to upgrade Power BI Desktop, there is additional reason, May 2022 version is out of support. Currently supported versions are: Jan2023, May2023 and Sep2023.

asmaG
Frequent Visitor

i add a holidays table date that containt all days not to count but after that when a i made a datediff it doesn't take all except my holidays table

ShirinArshadnia
Helper II
Helper II

hi @asmaG 

If you are looking for a simple way you should have a Date Dimesion correctly connected to your facts tables .

so you can have a measure which can simply calculate the date diff between the two dates ,

(a good date dimension is the one which have working days (as a flag for example 1) and weekend days (0).

 

 

if this is helpful for you please mark it as a solution .

hello , i finally found a solution

here the dax function that i used after creating a date table that containt holidays days

 

test_wee = RoundDown(DateDiff(t_faits[DATE_1], t_faits[DATE_2], Day) / 7, 0) * 5 +
Mod(5 + Weekday(t_faits[DATE_2]) - Weekday(t_faits[DATE_1]), 5)- calculate ( COUNT ( calendrier[Date] ), FILTER ( calendrier,t_faits[DATE_1] <= calendrier[Date]
&& t_faits[DATE_2] >= calendrier[Date]
&& ( calendrier[isholiday] = 1)))

Helpful resources

Announcements
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!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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