Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
aggysmith
New Member

Various fiscal periods in Calendar table

Hi,

 

I need to add a column with fiscal periods P01 to P12. The tricky bit is that we have 4 and 5 week periods, but still only 12 periods in Financial year. P01 and P12 are usually more or less than 7 days weeks to fit within the financial year brackets.

Our Financial year starts on 01/11 and ends 31/10.

Another complication is that some periods are 4 some 5 weeks long, so we can catch up with 12 periods fiscal year and not eavey year is the same. For example FY24 was 4 week - 4 week - 5 week ... 4-4-5...4-4-5...4-4-5 pattern..., but FY25 is 4-5-4...4-5-4...4-5-4...4-5-4.

Is there a DAX formula that will enable me to create this patterns in Power BI instead of uploading manual excel file with the Period number.

Could I also use similar logic for the Quarter numbers as they will be dependant on Period numbers rather than actual date.

 

Thank you

5 REPLIES 5
v-xingshen-msft
Community Support
Community Support

Hi All,
Firstly  Kedar_Pande and Sahir_Maharaj thank you for yours solutions!
And @aggysmith , 
 As I understand it you want to form a new FY column based on Nov 1 to Oct 31 and give them P01 to P12 values according to the pattern you need, and then form a new FY quarterly column based on that, right?
Here are some of my thoughts I used November 1, 2024 to October 31, 2025 as an example, hope this helps!

 

Fiscal Period = 
VAR FYStartDate = DATE(
    IF(MONTH('CalendarTable'[Date]) >= 11, YEAR('CalendarTable'[Date]), YEAR('CalendarTable'[Date]) - 1),
    11, 1
)
VAR CurrentDate = 'CalendarTable'[Date]
VAR DaysSinceFYStart = DATEDIFF(FYStartDate, CurrentDate, DAY)
VAR TotalWeeks = QUOTIENT(DaysSinceFYStart, 7) + 1
VAR Pattern = 
    IF(
        MOD(YEAR(FYStartDate), 2) = 0, // 
        "445445445445",
        "454454454454"  // 
    )
VAR WeekOffsets = 
    ADDCOLUMNS(
        GENERATESERIES(1, 12, 1),
        "Weeks",
        SWITCH(
            VALUE(MID(Pattern, [Value], 1)),
            4, 4,
            5, 5,
            0
        )
    )
VAR StartWeeks = 
    ADDCOLUMNS(
        WeekOffsets,
        "StartWeek", SUMX(
            FILTER(WeekOffsets, [Value] < EARLIER([Value])),
            [Weeks]
        ) + 1,
        "EndWeek", SUMX(
            FILTER(WeekOffsets, [Value] <= EARLIER([Value])),
            [Weeks]
        )
    )
VAR PeriodNumber = 
    MAXX(
        FILTER(
            StartWeeks,
            TotalWeeks >= [StartWeek] && TotalWeeks <= [EndWeek]
        ),
        [Value]
    )
RETURN "P" & FORMAT(PeriodNumber, "00")

Fiscal Quarter = "Q" & ROUNDUP(
    VALUE(SUBSTITUTE('CalendarTable'[Fiscal Period], "P", "0"))  / 3,0)

 

vxingshenmsft_0-1732158143811.png

If you have further questions, you can check the Pbix file I uploaded, I hope my solution can solve your problem, if I can solve your problem I will be honored!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @v-xingshen-msft , thank you for your solution, this is the closest to make my various weeks paterns in periods work. The last remining issue is the inconsistent lenght of week 1 of the fiscal year and week 52, both of which have non standard lenght of more or less than 7 days.

Standard period begins on Monday and ends after 4 weeks on a Friday, however , because the FY25 started on a Friday, the 1st week week will go all the way till 8th of November and therefore the last day of P01 should be a Friday as well, 29th November.  P02 should then start on a Sturday 30th November.

Similar situation would be at the end of each october (in 2026 it's luckly finishing on a Friday, but future years would be a problem in FY24 week 52 was 13 days long). In your solution also the very last day does only come up with a "P", but no period number attached to it. Which i think has something to do with the extra day in P01

aggysmith_0-1732181042838.png

 

The issue I have is also making it work for all the future years. The nature of our business is long term planning of the property development workflow and some projects can take as long as 10 years +. To make this model future proof, I need to make this paterns work up untill at least 2050. 

This is beyond my Power BI competence, so really hoping for yours and others expertise 🙂

 

Thank you all in advance!

Kedar_Pande
Super User
Super User

@aggysmith 

Create the Calculated Column for Fiscal Periods

FiscalPeriod = 
VAR StartOfFiscalYear = DATE(YEAR('Calendar'[Date]) - IF(MONTH('Calendar'[Date]) < 11, 1, 0), 11, 1)
VAR DayOfYear = DATEDIFF(StartOfFiscalYear, 'Calendar'[Date], DAY)
VAR FiscalPattern = SWITCH(
TRUE(),
YEAR('Calendar'[Date]) = 2024, "4-4-5",
YEAR('Calendar'[Date]) = 2025, "4-5-4",
BLANK()
)

RETURN
SWITCH(
FiscalPattern,
"4-4-5",
SWITCH(
TRUE(),
DayOfYear <= 28, "P01",
DayOfYear <= 56, "P02",
DayOfYear <= 91, "P03",
DayOfYear <= 119, "P04",
DayOfYear <= 147, "P05",
DayOfYear <= 182, "P06",
DayOfYear <= 210, "P07",
DayOfYear <= 238, "P08",
DayOfYear <= 273, "P09",
DayOfYear <= 301, "P10",
DayOfYear <= 329, "P11",
"P12"
),
"4-5-4",
SWITCH(
TRUE(),
DayOfYear <= 28, "P01",
DayOfYear <= 63, "P02",
DayOfYear <= 91, "P03",
DayOfYear <= 126, "P04",
DayOfYear <= 154, "P05",
DayOfYear <= 189, "P06",
DayOfYear <= 217, "P07",
DayOfYear <= 252, "P08",
DayOfYear <= 280, "P09",
DayOfYear <= 315, "P10",
DayOfYear <= 343, "P11",
"P12"
),
BLANK()
)

Add another calculated column for quarters:

FiscalQuarter = 
SWITCH(
'Calendar'[FiscalPeriod],
"P01", "Q1",
"P02", "Q1",
"P03", "Q1",
"P04", "Q2",
"P05", "Q2",
"P06", "Q2",
"P07", "Q3",
"P08", "Q3",
"P09", "Q3",
"P10", "Q4",
"P11", "Q4",
"P12", "Q4",
BLANK()
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

aggysmith
New Member

Hi @Sahir_Maharaj 

Thank you for your prompt advise.

I used the solution as sugested, but clearly am missing sumthing (perhaps not as advanced in Power BI and hence dont quite understand).

I keep getting error and this is due to the bottom section of your solution

aggysmith_0-1732110903553.png

I'm not quite sure where to take it from here.

 

Any fiurther advice will be appreciated.

 

Best regards.

Sahir_Maharaj
Super User
Super User

Hello @aggysmith,

 

Can you please try this approach:

Fiscal Period = 
VAR FiscalStartDate = DATE(YEAR('Calendar'[Date]) - IF(MONTH('Calendar'[Date]) < 11, 1, 0), 11, 1)
VAR DaysSinceFiscalStart = DATEDIFF(FiscalStartDate, 'Calendar'[Date], DAY)

VAR PeriodPattern = "445445445445"

VAR CumulativeDays = 
    GENERATESERIES(0, 364, 28) + -- For 4 weeks
    GENERATESERIES(28, 364, 35) -- For 5 weeks

VAR PeriodNumber = 
    LOOKUPVALUE(
        [Period],
        [DaysThreshold], TRUE,
        [DaysSinceFiscalStart] <= [CumulativeDays]
    )
RETURN
PeriodNumber
)

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.