Forum Discussion
Responsive ResidentAdd 13 Fiscal Periods and Fiscal Week into DimDate Table
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
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....
4 Replies
VelesAdvocate V
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
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.
jpt1228Looks 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.