substract days from date excluding week ends

[vijaykumarj19]

i have few columns

c1	c2	c3	c4	c5	c6
1	21-08-2020	11	07-08-2020	22-10-2020	2-7-2020
2	10-08-2020	6	03-08-2020	10-10-2020	03-10-2020
3	17-08-2020	13	30-07-2020	12-08-2020	04-10-2020

expected

C4 = C2-C3 Excluding weekends

output table unpivot

c1	Attr	value
1	c4	07-08-2020
1	c5	22-10-2020
1	c6	2-7-2020
2	c4	03-08-2020
2	c5	10-10-2020
2	c6	03-10-2020
3	c4	30-07-2020
3	c5	12-08-2020
3	c6	04-10-2020

[Anonymous]

after reading the tables more carefully,try this function

let
  subWD = (d, nd) => 
    let
      pwe = Date.AddDays(Date.EndOfWeek(d, Day.Monday), - 7),
      chk = Duration.Days(d - pwe),
      nwein = (Number.IntegerDivide(nd - chk, 5) + Number.From(Number.Mod(nd - chk, 5) > 0))
    in
      Date.AddDays(d, - nd - nwein * 2 + 1)
in
  subWD

ps

this is a math-like solution: tangled but synthetic formulas.
A simpler and clearer but more verbose IT solution is possible by using  functions such as List.Dates, date.endofweek,

list. difference and few others.

[v-xuding-msft]

Hi @vijaykumarj19 ,

You could follow the very detailed steps from this blog: https://www.skillwave.training/networkdays/ to have a try.

[Greg_Deckler]

@vijaykumarj19 - 

Maybe:

C4 =
  VAR __Table = FILTER(ADDCOLUMNS(CALENDAR([C2] - [C3]*1.5,[C2]),"Weekday",IF(WEEKDAY([Date],2)<6,1,0)),[Weekday]=1)
  VAR __Table1 = ADDCOLUMNS(__Table,"Count",COUNTROWS(FILTER(__Table,[Date]>=EARLIER([Date]))))
RETURN
  MAXX(FILTER(__Table1,[Count] = [C3]),[Date])

PBIX is attached below sig. Table (15).

[vijaykumarj19]

There is no table 15 in the .PBIX file you shared

and can we create same column in query editor