topic Re: Various fiscal periods in Calendar table in DAX Commands and Tips

Various fiscal periods in Calendar table

aggysmith — Wed, 20 Nov 2024 13:17:53 GMT

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

Re: Various fiscal periods in Calendar table

Sahir_Maharaj — Wed, 20 Nov 2024 13:27:32 GMT

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 )

Re: Various fiscal periods in Calendar table

aggysmith — Wed, 20 Nov 2024 13:56:45 GMT

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

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

Any fiurther advice will be appreciated.

Best regards.

Re: Various fiscal periods in Calendar table

Kedar_Pande — Wed, 20 Nov 2024 17:28:00 GMT

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

Re: Various fiscal periods in Calendar table

Anonymous — Thu, 21 Nov 2024 03:04:00 GMT

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.

Re: Various fiscal periods in Calendar table

aggysmith — Thu, 21 Nov 2024 09:28:27 GMT

Hi Anonymous , 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!