Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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..
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:
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:
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!
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..
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |