cancel
Showing results for
Did you mean:
Frequent Visitor

## 4-4-5 Calendar table with Jan 1st as start of the year and every week starting on Saturday

Hi, so here is my situation. I would like to create a Calendar table.

• 4-4-5 months
• Jan 1st is start of year
• Dec 31st is end of year
• Every week after Jan 1st ends on Friday until Dec 31st and begins on Saturday

Sample date table:

 4-4-5 Month Month Start Date Month End Date 1/1/2018 1/1/2018 1/26/2018 2/1/2018 1/27/2018 2/23/2018 3/1/2018 2/24/2018 3/30/2018 4/1/2018 3/31/2018 4/27/2018 5/1/2018 4/28/2018 5/25/2018 6/1/2018 5/26/2018 6/29/2018 7/1/2018 6/30/2018 7/27/2018 8/1/2018 7/28/2018 8/24/2018 9/1/2018 8/25/2018 9/28/2018 10/1/2018 9/29/2018 10/26/2018 11/1/2018 10/27/2018 11/23/2018 12/1/2018 11/24/2018 12/31/2018 1/1/2019 1/1/2019 1/25/2019 2/1/2019 1/26/2019 2/22/2019 3/1/2019 2/23/2019 3/29/2019 4/1/2019 3/30/2019 4/26/2019 5/1/2019 4/27/2019 5/24/2019 6/1/2019 5/25/2019 6/28/2019 7/1/2019 6/29/2019 7/26/2019 8/1/2019 7/27/2019 8/23/2019 9/1/2019 8/24/2019 9/27/2019 10/1/2019 9/28/2019 10/25/2019 11/1/2019 10/26/2019 11/22/2019 12/1/2019 11/23/2019 12/31/2019

2 REPLIES 2
Community Support Team

## Re: 4-4-5 Calendar table with Jan 1st as start of the year and every week starting on Saturday

Hi @brownmamba,

One sample for your reference, Please check the following steps as below.

1. Create a CALENDAR table and a calcualted table. Then create relationship between tables.

`Date = CALENDARAUTO()`
`Table 2 = VALUES(Table1[4-4-5 Month])`

2. Create three calculated column in the Fact table. (Table 1)

`pre = DATEADD('Table1'[4-4-5 Month],-1,MONTH)`
`endmonth = ENDOFMONTH('Date'[Date])`
```Month Start Date =
VAR result =
CALCULATE (
MAX ( 'Date'[Date] ),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] >= Table1[pre]
&& 'Date'[Date] <= Table1[endmonth]
&& WEEKDAY ( 'Date'[Date] ) = 7
)
)
VAR MOnth =
MONTH ( 'Table1'[4-4-5 Month] )
VAR day =
DAY ( 'Table1'[4-4-5 Month] )
RETURN
IF ( MOnth = 1 && day = 1, 'Table1'[4-4-5 Month], result )
```

3. Create two calculated columns in the new calculated table. (Table 2)

`EndOfMonth = ENDOFMONTH('Date'[Date])`
```Month End Date =
CALCULATE (
MAX ( 'Date'[Date] ),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] >= 'Table 2'[4-4-5 Month]
&& 'Date'[Date] <= 'Table 2'[EndOfMonth]
&& WEEKDAY ( 'Date'[Date] ) = 6
)
)
```

4.  Then we can get the result table as we need by create another new table and create two calcualetd columns in it.

`Calendar = VALUES(Table1[4-4-5 Month])`
`Month End Date = LOOKUPVALUE('Table 2'[Month End Date],'Table 2'[4-4-5 Month],'Calendar'[4-4-5 Month])`
`Month Start Date = LOOKUPVALUE(Table1[Month Start Date],Table1[4-4-5 Month],'Calendar'[4-4-5 Month])`

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

## Re: 4-4-5 Calendar table with Jan 1st as start of the year and every week starting on Saturday

Thank you! It looks good for the most part but there seems to be some discrepancies on some of the rows. For example the following 4-4-5 month rows are off:

• the 8/1/2018 ends on 8/31/2018 when it should end on 8/24 (even though subsequent start date is correct at 8/25/2018)
• the 12/1/2018 4-4-5 month row should be 11/24/2018 start to 12/31/2018 end
• the 5/1/2019 row should contain 4 weeks instead of 5 and should end on 5/24/2019
• both 8/1/2019 and 9/1/2019 rows are off with the correct start and end dates being 8/23/2019 and 8/24/2019, respectively
• the 11/1/2019 row should end with 11/22/2019