Forum Discussion

vijaykumarj19's avatar
vijaykumarj19
Microsoft Employee
6 years ago

substract days from date excluding week ends

i have few columns

c1       c2   c3         c4            c5          c6
121-08-2020   11    07-08-2020      22-10-2020      2-7-2020
210-08-2020    6    03-08-2020      10-10-2020      03-10-2020
317-08-2020    13    30-07-2020      12-08-2020      04-10-2020

 

expected

C4 = C2-C3 Excluding weekends

 

output table unpivot

c1Attrvalue 
1c407-08-2020 
1c522-10-2020 
1c62-7-2020 
2c403-08-2020 
2c510-10-2020 
2c603-10-2020 
3c430-07-2020 
3c512-08-2020 
3c604-10-2020 

 

 

 

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      vijaykumarj19
      Microsoft Employee
      6 years ago

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

      and can we create same column in query editor

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    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.