Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
Hi @vijaykumarj19 ,
You could follow the very detailed steps from this blog: https://www.skillwave.training/networkdays/ to have a try.
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).
There is no table 15 in the .PBIX file you shared
and can we create same column in query editor
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.