Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Good day!
I have a matrix where I'm returning scheduled hours for employees (please see screenshot below)
I have a date table defined as below:
TableDT =
VAR MinYear = YEAR ( MIN ('CW_Scheduled Hours'[Date] ) )
VAR MaxYear = YEAR ( MAX ('CW_Scheduled Hours'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Week Number", WEEKNUM ( [Date] ),
"Year Number", YEAR ( [Date] ),
"Index", 12 * YEAR ( [Date] ) + MONTH ( [Date] ))
I also have a calculated measure (thanks to the help of another user) to provide my column headers by week:
Year-WeekNumber = SWITCH(
TRUE(),
[Week Number] < 10, [Year Number] & "-0" & [Week Number],
[Week Number] >= 10, [Year Number] & "-" & [Week Number] )
I was wondering how I might limit the matrix values to the current week and only the next 4. Is there a way to do this without changing my SQL query that pulls the data?
THANK YOU
Solved! Go to Solution.
@Anonymous,
One approach is to create a Relative Week column in the date table, and set a filter on the visual to filter Relative Week between 0 and 4. Here's the calculated table with Relative Week:
TableDT =
VAR MinYear = YEAR ( MIN ('CW_Scheduled Hours'[Date] ) )
VAR MaxYear = YEAR ( MAX ('CW_Scheduled Hours'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Week Number", WEEKNUM ( [Date] ),
"Year Number", YEAR ( [Date] ),
"Index", 12 * YEAR ( [Date] ) + MONTH ( [Date] ),
"Relative Week",
VAR vToday = TODAY()
VAR vWeekEndDate =
[Date] - WEEKDAY ( [Date], 1 ) + 7
VAR vWeekEndDateToday =
vToday - WEEKDAY ( [Date], 1 ) + 7
VAR vResult =
CONVERT ( ( vWeekEndDate - vWeekEndDateToday ) / 7, INTEGER )
RETURN
vResult
)
Proud to be a Super User!
I apologize for the late reply. I wound up coming down with COVID!
So, this works perfectly. I'm going to accept as solution. I was going to post an ask to help me walk me through this but when I looked up the WEEKDAY function and then plugged in numbers, I saw the logic. Thank you!
@Anonymous,
Hope you've fully recovered from COVID. Glad to hear the solution works!
Proud to be a Super User!
@Anonymous,
One approach is to create a Relative Week column in the date table, and set a filter on the visual to filter Relative Week between 0 and 4. Here's the calculated table with Relative Week:
TableDT =
VAR MinYear = YEAR ( MIN ('CW_Scheduled Hours'[Date] ) )
VAR MaxYear = YEAR ( MAX ('CW_Scheduled Hours'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Week Number", WEEKNUM ( [Date] ),
"Year Number", YEAR ( [Date] ),
"Index", 12 * YEAR ( [Date] ) + MONTH ( [Date] ),
"Relative Week",
VAR vToday = TODAY()
VAR vWeekEndDate =
[Date] - WEEKDAY ( [Date], 1 ) + 7
VAR vWeekEndDateToday =
vToday - WEEKDAY ( [Date], 1 ) + 7
VAR vResult =
CONVERT ( ( vWeekEndDate - vWeekEndDateToday ) / 7, INTEGER )
RETURN
vResult
)
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
90 | |
84 | |
76 | |
64 |
User | Count |
---|---|
145 | |
109 | |
109 | |
102 | |
96 |