cancel
Showing results for
Did you mean:
Post Prodigy

Fiscal week in power query

Hi

How to calculate fiscal week in calender in power query not in tble view , Week start day should be Monday and Fiscal month is Octomber

AB
1 ACCEPTED SOLUTION
Super User IV

You can add a custom column in the query editor and put this formula in the pop-up box.  Replace [Date] with [YourDateColumnName] if needed.

let
FY = if Date.Month([Date]) < 10 then Date.Year([Date]) -1 else Date.Year([Date]),
FirstMonday = List.Select(List.Transform({0..6}, each Date.AddDays(#date(FY, 10,1),_)), each Date.DayOfWeekName(_) = "Monday"){0}
in
Number.RoundUp((1+Duration.TotalDays([Date] - FirstMonday))/7,0)

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

2 REPLIES 2
Super User IV

You can add a custom column in the query editor and put this formula in the pop-up box.  Replace [Date] with [YourDateColumnName] if needed.

let
FY = if Date.Month([Date]) < 10 then Date.Year([Date]) -1 else Date.Year([Date]),
FirstMonday = List.Select(List.Transform({0..6}, each Date.AddDays(#date(FY, 10,1),_)), each Date.DayOfWeekName(_) = "Monday"){0}
in
Number.RoundUp((1+Duration.TotalDays([Date] - FirstMonday))/7,0)

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Post Prodigy

Thank you Sir

AB

Announcements