cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brownmamba Frequent Visitor
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

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

 

 

 

 

2 REPLIES 2
Community Support Team
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.PNG

 

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])

Capture.PNG

 

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.
brownmamba Frequent Visitor
Frequent Visitor

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

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