How to create a table that shows dates starting from Sunday and goes to future Saturady?

[zebulajams]

Trying to create a Matrix that shows timecards entered throughout the week. I'd like it to look something like:

  ________________Sunday________Monday________Tuesday______Wednesday______Thursday______Friday_______Saturday____Total

John Doe         |      0.00        |        8.00           |          8.00       |        8.00          |          8.00      |        8.00       |       0.00   |    40.00

John Morris     |      0.00        |        8.00           |         8.00        |       0.00           |         8.00       |       8.00        |      0.00    |    32.00

Eric LeCarde    |      0.00        |        0.00            |         0.00        |       8.00           |         8.00       |       8.00        |      0.00   |    24.00

___________________________________________________________________________________________________________________________________

This way the supervisor can audit and see if their employee has missed an entry somewhere in the week. I pretty much have everything set up, I just don't know how to set the calendar to show the week starting on Sunday and the coming days all the way to Saturday.

Any ideas?                                                     

[Whitewater100 · Accepted solution]

Hi:

You can use this date table code. MODELING>NEW TABLE 

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

I beleive it has columns you need and week starts on Sunday. I hope this helps..

[Whitewater100 · Accepted solution]

Hi:

To complete my anser..

Once you have Date Table and Markas Date Table.

Click on Day Name and Choose "Sort by Column" where you will choose Day of Week. Please see images below:

[zebulajams]

This is fantastic so far! The only thing is, I'm noticing that it is grabbing the previous dates after the current date rather than future dates. So, instead of:

Sunday           Monday         Tuesday       Wednesday  Thursday       Friday            Saturday

05/29/2022 | 05/30/2022 | 05/31/2022 | 06/01/2022 | 06/02/2022 | 06/03/2022 | 06/04/2022 | 

It is showing:

Sunday           Monday         Tuesday       Wednesday  Thursday       Friday            Saturday

05/29/2022 | 05/30/2022 | 05/31/2022 | 05/25/2022 | 05/26/2022 | 05/27/2022 | 05/28/2022 | 

Is there something I need to change to make it only grab the current week?

UPDATE: I added the "Date" to the filters section and set it to "is in this week" and it returned exactly what I needed. Thank you so much for your help!!!

[Whitewater100]

Hi:

Thank you!

You can put another Calc Col in your Date Table:

Current Week in Date Table CC = 

Curr Week =

var cweek = WEEKNUM(TODAY())

return

IF(Dates[Week No.] =cweek,

"Current", CONVERT(Dates[Week No.],STRING))

Use this for your slicer and select current week from filter pane and it will always open to current week. I hope this helps!

[Whitewater100 · Accepted solution]

Hi:

You can use this date table code. MODELING>NEW TABLE 

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

I beleive it has columns you need and week starts on Sunday. I hope this helps..