Week number for Qtr

[KimC]

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)

[Melissa]

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

[KimC]

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

[markperrone]

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

[KimC]

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

Hi @KimC,

That’s an important detail, I’ll look into that when I get the chance. Will get back to you.

[Melissa]

Hi @KimC,

I’ve replaced your Date table M code with the Extended date table, you can find here.

Extended Date Table (Power Query M function)

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.

[KimC]

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

[Melissa]

Glad I could help @KimC

[KimC]

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

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

[KimC]

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