Calculate Financial Period End Date

[mcor13]

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

[Gabry · Accepted solution]

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
    )

[mcor13]

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

[lbendlin]

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