Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
zebulajams
New Member

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

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?                                                     

 

2 ACCEPTED SOLUTIONS
Whitewater100
Solution Sage
Solution Sage

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

 

View solution in original post

Whitewater100
Solution Sage
Solution Sage

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:

Whitewater100_0-1654016023978.pngWhitewater100_1-1654016075445.png

 

View solution in original post

4 REPLIES 4
Whitewater100
Solution Sage
Solution Sage

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:

Whitewater100_0-1654016023978.pngWhitewater100_1-1654016075445.png

 

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

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
Solution Sage
Solution Sage

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

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.