topic Re: calculate number of days between 2 dates except weekend and free days in Report Server

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

asmaG — Tue, 07 Nov 2023 10:37:53 GMT

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

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

ShirinArshadnia — Tue, 07 Nov 2023 10:56:03 GMT

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 .

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

asmaG — Tue, 07 Nov 2023 11:05:15 GMT

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

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

josef78 — Tue, 07 Nov 2023 19:08:51 GMT

Try NETWORKDAYS function

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

asmaG — Tue, 07 Nov 2023 19:19:15 GMT

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)

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

josef78 — Tue, 07 Nov 2023 19:25:40 GMT

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

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

asmaG — Wed, 08 Nov 2023 08:57:48 GMT

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 😕

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

josef78 — Thu, 09 Nov 2023 09:59:03 GMT

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.

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

asmaG — Mon, 13 Nov 2023 09:22:10 GMT

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