Week number for Qtr

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

Thank you Melissa, you are a rock star. That works perfectly :grinning:

2 Likes

Whoa – I have like so much to learn. Very nice. Adding this to my template

1 Like

@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?

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.

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

@Melisa, thank you so much. I am trying to get through all the study material but it is a slow process and I haven’t got to dates yet. In the interim, you have helped me so much to solve my problem for work.
:pray::pray::pray::pray::pray::pray::pray::pray::pray::pray::pray::pray::pray::pray::pray:

1 Like

Glad I could help @KimC
:+1:

@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 :frowning:

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 :wink:

1 Like

Absolutely perfect. Can’t thank you enough @Melissa.

1 Like