topic Re: Calculate Financial Period End Date in DAX Commands and Tips

Calculate Financial Period End Date

mcor13 — Wed, 29 Jan 2025 17:55:09 GMT

I'm tring to add a calculated column to a date table to define the financial period for a year that starts 1st April.

Period 1 ends on the 4th Saturday of April then periods end using the following

2	4 weeks
3	5 weeks
4	4 weeks
5	4 weeks
6	5 weeks
7	4 weeks
8	4 weeks
9	5 weeks
10	4 weeks
11	4 weeks
12	all days up to 31st March

I was thinking that if I could define the 4th Saturday of the year , anything less than or equal to that would be P1,

then anything less than P1 + 28 would be P2 etc....

Any help or suggestions would be very much appreciated.

Thanks

Re: Calculate Financial Period End Date

lbendlin — Wed, 29 Jan 2025 22:04:24 GMT

These dates are immutable. There is no point to do this in DAX or Power Query. Use an external, precomputed reference table.

Re: Calculate Financial Period End Date

Gabry — Wed, 29 Jan 2025 22:07:35 GMT

Hey buddy,

could you try this?

Period = VAR ThisDate = 'Date'[Date] VAR FY = YEAR(ThisDate) - IF(MONTH(ThisDate) < 4, 1, 0) VAR StartOfFY = DATE(FY, 4, 1) VAR FirstSaturday = StartOfFY + MOD(6 - WEEKDAY(StartOfFY, 2), 7) VAR FourthSaturday = FirstSaturday + 21 VAR BoundP1 = FourthSaturday VAR BoundP2 = BoundP1 + 28 VAR BoundP3 = BoundP2 + 35 VAR BoundP4 = BoundP3 + 28 VAR BoundP5 = BoundP4 + 28 VAR BoundP6 = BoundP5 + 35 VAR BoundP7 = BoundP6 + 28 VAR BoundP8 = BoundP7 + 28 VAR BoundP9 = BoundP8 + 35 VAR BoundP10 = BoundP9 + 28 VAR BoundP11 = BoundP10 + 28 VAR BoundP12 = DATE(FY + 1, 3, 31) RETURN SWITCH( TRUE(), ThisDate <= BoundP1, 1, ThisDate <= BoundP2, 2, ThisDate <= BoundP3, 3, ThisDate <= BoundP4, 4, ThisDate <= BoundP5, 5, ThisDate <= BoundP6, 6, ThisDate <= BoundP7, 7, ThisDate <= BoundP8, 8, ThisDate <= BoundP9, 9, ThisDate <= BoundP10, 10, ThisDate <= BoundP11, 11, 12 )

Re: Calculate Financial Period End Date

mcor13 — Thu, 30 Jan 2025 11:13:53 GMT

That's worked nicely. Thank you Gabry Your help is very much appreciated.