I would like to have a week number for the Quarter. I have used a column formula
WeekOfQtr = ROUNDUP(MOD([Week Number],MAX([Week Number])/4),0)
The formula works well, except for the last few days of the month which returns to 1 instead of 14.
Does anyone have suggestions how to fix this?
Try this as Calculated Column in your Dates table:
Week of Quarter =
VAR Q = Dates[QuarterOfYear]
VAR Y = Dates[Year]
Dates[QuarterOfYear] = Q &&
Dates[Year] = Y
I hope this is helpful
Thank you Melissa, you are a rock star. That works perfectly
Whoa – I have like so much to learn. Very nice. Adding this to my template
@Melisa, If my Financial year starts at 1 Nov instead of 1 Jan, the above code will not work as it starts the week number from Jan instead of Nov. It works fine for the remaining quarters. How could I modify the code to get this to work?
That’s an important detail, I’ll look into that when I get the chance. Will get back to you.
I’ve replaced your Date table M code with the Extended date table, you can find here.
To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know!
// August 2022
There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub ( follow this link) you can copy a version of Extended Date table which returns a r…
Next I added a new dimension called
CalcForWkInQ which is an interger:
[Fiscal Year]*10000 + [Fiscal Quarter]*1000 + [WeekOffset]
Amended the logic for the Calculated Column slightly.
Week in Quarter =
VAR Q = Dates[Fiscal Quarter]
VAR Y = Dates[Fiscal Year]
Dates[Fiscal Quarter] = Q &&
Dates[Fiscal Year] = Y
Here’s your updated sample file.
WeekOfQtr v2.pbix (101.7 KB)
I hope this is helpful.
@Melisa, Could I trouble you for one more favour? I am trying to create a column without success for Fiscal Year and Mth as YYYYMM format - Nov 2020 would be 202001.
I have sat here for the last hour trying to work it out
added this logic to the Date table M function:
InsertCalcForWkinQ, "Fiscal Year & Month", each
(if [MonthOfYear] >= FYStartMonth then [Year] +1 else [Year]) *100 + [Fiscal Period],
With this result.
Here’s your updated file.
WeekOfQtr v3.pbix (103.2 KB)
I hope this is helpful
Absolutely perfect. Can’t thank you enough