Forum Discussion
Solved
Calculate Financial Period End Date
Hi
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
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 )
3 Replies
- Gabry
Super User
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 ) - lbendlin
These dates are immutable. There is no point to do this in DAX or Power Query. Use an external, precomputed reference table.
