Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
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)
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
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!
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
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
I'm not quite sure where to take it from here.
Any fiurther advice will be appreciated.
Best regards.
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
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |