Add 13 Fiscal Periods and Fiscal Week into DimDate Table

[jpt1228]

Hello, I Need to add Fiscal Periods and Fiscal weeks which tie out to the ISO calendar.

I need to figure out how to add the fiscal periods based on the ISO week.

Period 1 = ISO Weeks 1,2,3,4

Period 2 = ISO Weeks 5,6,7,8

Period 3 = ISO Weeks 9,10,11,12

Period 4 = ISO Weeks 13,14,15,16

Period 5 = ISO Weeks 17,18,19,20

P6 = 21,22,23,24

P7 = 25,26,27,28

P8 = 29,30,31,32

P9 = 33,34,35,36

P10 = 37,38,39,40

P11 = 41,42,43,44

P12 = 45,46,47,48

P31 = 49,50,51,52

Also will need to filter dashboards based on the current period.

I have a DimDate table with a number of different columns currently.

Thanks

Jon

[Veles · Accepted solution]

Try this

Period =
SUMX (
 FILTER (
 ALL ( DimDate ),
 DimDate[Year] = EARLIER ( DimDate[Year] )
 && DimDate[Date] <= EARLIER ( DimDate[Date] )
 ),
 IF ( MOD ( DimDate[Week in Year], 4 ) = 1, 1, 0 )
)

Essentially this is using a SUMX to count the number of triggers for a new period have happened so far in the year. The trigger being when the remainder of the week number/4 is 1.

EDIT: Or just rounding up the week number/4 to the nearest whole number would be much easier....

[Veles · Accepted solution]

Try this

Period =
SUMX (
 FILTER (
 ALL ( DimDate ),
 DimDate[Year] = EARLIER ( DimDate[Year] )
 && DimDate[Date] <= EARLIER ( DimDate[Date] )
 ),
 IF ( MOD ( DimDate[Week in Year], 4 ) = 1, 1, 0 )
)

Essentially this is using a SUMX to count the number of triggers for a new period have happened so far in the year. The trigger being when the remainder of the week number/4 is 1.

EDIT: Or just rounding up the week number/4 to the nearest whole number would be much easier....

[jpt1228]

Looks like I have a 0 Period - Which Should be period 1, but if I add ISO week +1 that will give me 1-14 periods.

[jpt1228 · Accepted solution]

Hello @Veles your solution for dividing the week in year works. I substituted the ISO Week

Period in year = ROUNDUP(DimDate[ISO Week in Year],0) / 4

I am hoping to be able to use last 3 periods, sales VS same period last year. I will have to test this out.

[Veles]

I usually have another column that is the year and period together (i.e. 201901, 201902, etc.). When referring to the equivalent period last year you then just have to subtract 100 from the current period.