Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everybody.
Is it possible to determine, if an employee has worked the last 3 weekends based on this information?
Date | Staff Nr. | Count |
02.05.2020 | 125 | 1 |
03.05.2020 | 125 | 1 |
I have a calendar table that contains data for Weekend (Weekday 6 & 7) and Weeknumer (1-53).
In the end I need a column that contains "Yes" or "No", if the employee has worked the last 3 weekends.
Best Regards
Solved! Go to Solution.
Here is one way to do it. Assuming you have a Date table with a relationship to the Date field in your StaffData table.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
// Assumption:
// Calendar connected via Date to T[Date] via 1:*.
// Weeknumber in your Calendar must not restart with
// each year. It must number weeks from the first one
// to the last one. If your Weeknumber restarts in
// each year, please create a column with a continuous
// numbering of weeks and use it instead.
[Worked in Last 3 Weekends] =
var __person = T[Staff Nr]
var __currentWeeknumber = related( 'Calendar'[Weeknumber] )
var __currentDayIsSunday =
( related( 'Calendar'[Weekday] ) = 7 )
var __weekendDaysInLast3Weekends =
filter(
'Calendar'[Date],
'Calendar'[Weeknumber] in {
(__currentWeeknumber - 1) + __currentDayIsSunday,
(__currentWeeknumber - 2) + __currentDayIsSunday,
(__currentWeeknumber - 3) + __currentDayIsSunday
}
&&
'Calendar'[Weekday] in {6,7}
)
var __workedInLast3Weekends =
COUNTROWS(
INTERSECT(
SELECTCOLUMNS(
FILTER(
T,
T[Staff Nr] = __person
),
"@Date", T[Date]
),
__weekendDaysInLast3Weekends
)
) = 6 // working 3 full weekends
return
if( __workedInLast3Weekends,
"Yes",
"No"
)
Please note that if the day you're calculating this is Sunday, then the logic is different than when you calculate this on a different day. Hence the adjustment in the FILTER function of the form "+ __currentDayIsSunday."
Best
D
// Assumption:
// Calendar connected via Date to T[Date] via 1:*.
// Weeknumber in your Calendar must not restart with
// each year. It must number weeks from the first one
// to the last one. If your Weeknumber restarts in
// each year, please create a column with a continuous
// numbering of weeks and use it instead.
[Worked in Last 3 Weekends] =
var __person = T[Staff Nr]
var __currentWeeknumber = related( 'Calendar'[Weeknumber] )
var __currentDayIsSunday =
( related( 'Calendar'[Weekday] ) = 7 )
var __weekendDaysInLast3Weekends =
filter(
'Calendar'[Date],
'Calendar'[Weeknumber] in {
(__currentWeeknumber - 1) + __currentDayIsSunday,
(__currentWeeknumber - 2) + __currentDayIsSunday,
(__currentWeeknumber - 3) + __currentDayIsSunday
}
&&
'Calendar'[Weekday] in {6,7}
)
var __workedInLast3Weekends =
COUNTROWS(
INTERSECT(
SELECTCOLUMNS(
FILTER(
T,
T[Staff Nr] = __person
),
"@Date", T[Date]
),
__weekendDaysInLast3Weekends
)
) = 6 // working 3 full weekends
return
if( __workedInLast3Weekends,
"Yes",
"No"
)
Please note that if the day you're calculating this is Sunday, then the logic is different than when you calculate this on a different day. Hence the adjustment in the FILTER function of the form "+ __currentDayIsSunday."
Best
D
Thanks for your advice. The fact table is not really big. How would you calculate that with a dax measure?
Here is one way to do it. Assuming you have a Date table with a relationship to the Date field in your StaffData table.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
21 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
45 | |
15 | |
12 |