cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Responsive Resident

## Add 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

2 ACCEPTED SOLUTIONS

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....

Responsive Resident

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.

4 REPLIES 4

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....

Responsive Resident

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.

Responsive Resident

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.

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors