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 know you can use Date.EndOfMonth to get the last day of the month, but I need to grab the last Friday of each month, in other words the last working day of each month. Any help would be appreciated.
Solved! Go to Solution.
Hi @VQH,
you just need to add an additional condition. Something in lines with:
Date.DayOfWeekName(_)="Friday" and Number.From(Date.EndOfMonth(DateTime.From(_))) - _ < 7
This also checks that this is the last week week in the month (less than 7 days before the EOM).
Kind regards,
John
let
Source = #date(2022,8,8),
LastFriday=List.Last(List.Generate(()=>Date.EndOfMonth(Source),each Date.DayOfWeek(_)<>3,each Date.AddDays(_,-1)))
in
LastFriday
Hi @VQH ,
See below code how to get a list of date that is Friday:
let
StartDate = #date(2022,1,1),
EndDate = #date(2022,12,31),
Calendar = List.Transform(
List.Select(
{ Number.From(StartDate)..Number.From(EndDate) },
each
Date.DayOfWeekName(_)="Friday"
),
each
Date.From(_)
)
in
Calendar
Regards
KT
I can get the friday of each week no problem, how do I get the last Friday of each month? Example the last Friday of July 2022 is July 29 2022
Hi @VQH,
you just need to add an additional condition. Something in lines with:
Date.DayOfWeekName(_)="Friday" and Number.From(Date.EndOfMonth(DateTime.From(_))) - _ < 7
This also checks that this is the last week week in the month (less than 7 days before the EOM).
Kind regards,
John
Hi all,
also an interesting solution inspired by this post :
let
StartDate = 202201,
EndDate = 202212,
Calendar = List.Transform({StartDate..EndDate}, each Date.StartOfWeek(Date.EndOfMonth(Date.FromText(Text.From(_*100+1))), Day.Friday))
in
Calendar
Kind regards,
John
Thanks, this helped me figure it out. I just need to pass in this:
= each Date.StartOfWeek(Date.EndOfMonth([ColumnName]), Day.Friday)
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.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |