KimC
October 23, 2020, 7:09am
1
Hi,
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?
WeekOfQtr.pbix (46.5 KB)
Hi @KimC ,
Try this as Calculated Column in your Dates table:
Week of Quarter =
VAR Q = Dates[QuarterOfYear]
VAR Y = Dates[Year]
RETURN
RANKX(
FILTER(
Dates,
Dates[QuarterOfYear] = Q &&
Dates[Year] = Y
),
Dates[Week Number]
,
, ASC
, Dense
)
I hope this is helpful
3 Likes
KimC
October 23, 2020, 7:58am
3
Thank you Melissa, you are a rock star. That works perfectly
3 Likes
Whoa – I have like so much to learn. Very nice. Adding this to my template
1 Like
KimC
October 24, 2020, 6:55am
5
@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?
Melissa
October 24, 2020, 10:22am
6
Hi @KimC ,
That’s an important detail, I’ll look into that when I get the chance. Will get back to you.
Hi @KimC ,
I’ve replaced your Date table M code with the Extended date table, you can find here.
Hi everyone,
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]
RETURN
RANKX(
FILTER(
Dates,
Dates[Fiscal Quarter] = Q &&
Dates[Fiscal Year] = Y
),
Dates[CalcForWkInQ]
,
, ASC
, Dense
)
Here’s your updated sample file. WeekOfQtr v2.pbix (101.7 KB)
I hope this is helpful.
1 Like
KimC
October 25, 2020, 5:05am
8
1 Like
KimC
October 25, 2020, 7:38am
10
@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
Melissa
October 25, 2020, 8:24am
11
Hi @KimC ,
added this logic to the Date table M function:
InsertFYandM =
Table.AddColumn(
InsertCalcForWkinQ, "Fiscal Year & Month", each
(if [MonthOfYear] >= FYStartMonth then [Year] +1 else [Year]) *100 + [Fiscal Period],
Int64.Type
)
With this result.
Here’s your updated file. WeekOfQtr v3.pbix (103.2 KB)
I hope this is helpful
1 Like
KimC
October 25, 2020, 11:46am
12
Absolutely perfect. Can’t thank you enough @Melissa .
1 Like