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

[Anonymous]

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

Thank you in advance!!

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

[v-frfei-msft]

Hi @Anonymous,

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

[Anonymous]

Hi @v-frfei-msft, 

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 
• 12/1/2019 should start with 11/23/2019 and end with 12/31/2019

I know that's a lot of stuff but I think it could be stemming from some of the same issues Power Query-wise so hopefully that's the case.

Thanks!!