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.
Hello All,
Currently I'm working on a list which shows the working days of the month.
So what I would like is for it show the 1st of next month as well
But since refering to 2 parameters #year(which is 2020) and #month(which is 8), I'm not entirley sure how to add the first of next month.
This is the M code:
let
Source = List.Select(List.Dates(Date.StartOfMonth(Date.From(#date(#"Year",#"Month",Number.From(Date.Day(DateTime.FixedLocalNow()))))), Number.From(Date.Day(DateTime.FixedLocalNow())),#duration(1,0,0,0)),each Date.DayOfWeek(_)<5)
in
Source
Thanks for any help you might offer.
Solved! Go to Solution.
Hello @Anonymous
you can use List.Generate to generate your new list. Here the code
let
#"Year"= 2020,
#"Month"= 8,
Source = List.Select(List.Dates(Date.StartOfMonth(Date.From(#date(#"Year",#"Month",Number.From(Date.Day(DateTime.FixedLocalNow()))))), Number.From(Date.Day(DateTime.FixedLocalNow()))+3,#duration(1,0,0,0)),each Date.DayOfWeek(_)<5),
GenerateOneWorkingDayNextMonth = List.Select(List.Generate
(
()=> [NewDate = Source{0}, Counter = 0, FoundNextMonth= false],
each [Counter]<List.Count(Source),
each
[
FoundNextMonth = (#"Month"+1)= Date.Month(Date.AddDays([NewDate],1)),
NewDate= if [FoundNextMonth]= false then Source{[Counter]+1} else null,
Counter= [Counter]+1
],
each [NewDate]
), each _ <>null)
in
GenerateOneWorkingDayNextMonth
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
as function of a given month, to get all the working days and the first of next month
let
monthWorkingDays=(month)=>
let
m=#date(2020,month,1),
sd=Date.EndOfWeek(m,Day.Monday),
we={Date.AddDays(sd,-1),sd},
wends=List.Combine(List.Transform({0..4}, each {Date.AddDays(we{0},_*7),Date.AddDays(we{1},_*7)})),
mdays = List.Dates(m,Date.Day(Date.EndOfMonth(m)),#duration(1,0,0,0)),
wdays=List.Difference(mdays,wends)
in
wdays&{#date(2020,month+1,1)}
in
monthWorkingDays
It is not clear if you want the first day of next month or the first working day of next month
@Anonymous
One more solution:
= let
_Date =
List.Select(List.Dates(Date.StartOfMonth(Date.From(#date(2019,01, Number.From(Date.Day(DateTime.FixedLocalNow())))))
, Number.From(Date.Day(DateTime.FixedLocalNow())),#duration(1,0,0,0)),each Date.DayOfWeek(_)<5)
in
List.Combine({_Date,{ Date.AddDays(Date.EndOfMonth(List.Max(_Date)),1)}})
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
this function add to the working days list of current month the first working day of next month
let
monthWorkingDays=(month)=>
let
m=#date(2020,month,1),
sd=Date.EndOfWeek(m,Day.Monday),
we={Date.AddDays(sd,-1),sd},
wends=List.Combine(List.Transform({0..5}, each {Date.AddDays(we{0},_*7),Date.AddDays(we{1},_*7)})),
mdays = List.Dates(m,Date.Day(Date.EndOfMonth(m)),#duration(1,0,0,0)),
wdays=List.Difference(mdays,wends)
in
wdays&{List.Difference(List.Dates(#date(2020,month+1,1),3,#duration(1,0,0,0)),wends){0}}
in
monthWorkingDays
@Anonymous
One more solution:
= let
_Date =
List.Select(List.Dates(Date.StartOfMonth(Date.From(#date(2019,01, Number.From(Date.Day(DateTime.FixedLocalNow())))))
, Number.From(Date.Day(DateTime.FixedLocalNow())),#duration(1,0,0,0)),each Date.DayOfWeek(_)<5)
in
List.Combine({_Date,{ Date.AddDays(Date.EndOfMonth(List.Max(_Date)),1)}})
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
as function of a given month, to get all the working days and the first of next month
let
monthWorkingDays=(month)=>
let
m=#date(2020,month,1),
sd=Date.EndOfWeek(m,Day.Monday),
we={Date.AddDays(sd,-1),sd},
wends=List.Combine(List.Transform({0..4}, each {Date.AddDays(we{0},_*7),Date.AddDays(we{1},_*7)})),
mdays = List.Dates(m,Date.Day(Date.EndOfMonth(m)),#duration(1,0,0,0)),
wdays=List.Difference(mdays,wends)
in
wdays&{#date(2020,month+1,1)}
in
monthWorkingDays
It is not clear if you want the first day of next month or the first working day of next month
@Anonymous
Ahh yes, it should be the first day of next month, thanks
this function add to the working days list of current month the first working day of next month
let
monthWorkingDays=(month)=>
let
m=#date(2020,month,1),
sd=Date.EndOfWeek(m,Day.Monday),
we={Date.AddDays(sd,-1),sd},
wends=List.Combine(List.Transform({0..5}, each {Date.AddDays(we{0},_*7),Date.AddDays(we{1},_*7)})),
mdays = List.Dates(m,Date.Day(Date.EndOfMonth(m)),#duration(1,0,0,0)),
wdays=List.Difference(mdays,wends)
in
wdays&{List.Difference(List.Dates(#date(2020,month+1,1),3,#duration(1,0,0,0)),wends){0}}
in
monthWorkingDays
Hello @Anonymous
you can use List.Generate to generate your new list. Here the code
let
#"Year"= 2020,
#"Month"= 8,
Source = List.Select(List.Dates(Date.StartOfMonth(Date.From(#date(#"Year",#"Month",Number.From(Date.Day(DateTime.FixedLocalNow()))))), Number.From(Date.Day(DateTime.FixedLocalNow()))+3,#duration(1,0,0,0)),each Date.DayOfWeek(_)<5),
GenerateOneWorkingDayNextMonth = List.Select(List.Generate
(
()=> [NewDate = Source{0}, Counter = 0, FoundNextMonth= false],
each [Counter]<List.Count(Source),
each
[
FoundNextMonth = (#"Month"+1)= Date.Month(Date.AddDays([NewDate],1)),
NewDate= if [FoundNextMonth]= false then Source{[Counter]+1} else null,
Counter= [Counter]+1
],
each [NewDate]
), each _ <>null)
in
GenerateOneWorkingDayNextMonth
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
@Jimmy801
Is there away to make it generate the working days day by day instead of everything at once?
Thank you.
Hello @Anonymous
I used your query as starting point, because I didn't want to change your basics.
What exactly you mean day by day and not at once?
Jimmy
Hi @Jimmy801
Excuses me I was thinking about it the wrong way, I thought your code generated all of the working days at once instead of each day but that's cause it's end month now.
My apologies, thanks anyway!
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.