Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have two column - Start Date and Days required to deliver.
So if Start Date is 13/10/2023 which is on a friday and Days required to deliver is 2,so I need a new column called Arrival cut off date which should give me 17/10/2023 which excludes weekends and excludes counting the Start Date as 1st day. so Monday is 1st day and Tuesday 17th is 2nd day.
and during weekdays just add without exclution - so if start date is 10/10/2023 and Days required to deliver is 2 then Arrival cut off date which should give me 12/10/2023 which excludes the current date.
so I need to create a custom column in Power BI M query, not able to get a correct solution for this, please help
Solved! Go to Solution.
Thank you so much!!! this works ,
one more question - how to a set another parameter in the same m query , where there is a column delivery days and its values are
I didn't quite understand you, show the expected result in Excel
start | end | days required | Delivery days | arrival cut-off date |
13/10/2023 | 2 | Mon-Fri | 17/10/2023 | |
13/10/2023 | 2 | Mon-Sat | 16/10/2023 | |
13/10/2023 | 2 | Mon-Sun | 15/10/2023 | |
18/10/2023 | 2 | Tue-Thu | 23/10/2023 |
like the calculations that you have done was excluding weekends , so based on the delivery days column it is Mon-Fri, so based on what I set a condition I set for the row value which i set in the backet SQL , that is if column value is x then Mon-Fri if column value is y then Mon-Sat
So if Column value has Mon-Fri skip counting sat and sun , if Mon-Sat skip sunday
your calculation logic is incomprehensible to me
or this
List.Select(
List.Generate(()=>
[Start Date],(x)=>x<=Date.AddDays([Start Date],[Day]*5), (x)=> Date.AddDays(x,1)), (x)=> not (Date.DayOfWeek(x) =5 or Date.DayOfWeek(x) =6 or x=[Start Date])){[Day]-1}
and try this
List.Max( List.Range( List.Select(
List.Generate(()=>
[Start Date],(x)=>x<=Date.AddDays([Start Date],[Day]*5), (x)=> Date.AddDays(x,1)), (x)=> not (Date.DayOfWeek(x) =5 or Date.DayOfWeek(x) =6 or x=[Start Date])),0,[Day]))
User | Count |
---|---|
80 | |
74 | |
62 | |
61 | |
46 |
User | Count |
---|---|
108 | |
97 | |
88 | |
81 | |
61 |