Forum Discussion
buttercream
6 months agoHelper II
Need help creating a measure
Hello, My data looks like this: Employee Group Date Leave 112 Finance 1/7/2025 1 112 Finance 1/8/2025 1 112 Finance 1/11/2025 1 112 Finance 1/12/2025 1 112 F...
- 6 months ago
My apologies but I'm trying to calculate it by month. Example below.
Employee Group Date Leave 101 Finance 2/12/2025 1 101 Finance 2/13/2025 1 101 Finance 2/14/2025 1 101 Finance 2/15/2025 1 101 Finance 2/16/2025 1 101 Finance 3/20/2025 1 101 Finance 3/21/2025 1 101 Finance 3/22/2025 1 107 IT 1/22/2025 1 107 IT 1/23/2025 1 107 IT 1/24/2025 1 107 IT 1/25/2025 1 107 IT 2/3/2025 1 107 IT 2/4/2025 1 108 IT 2/1/2025 1 108 IT 2/2/2025 1 108 IT 2/3/2025 1 108 IT 2/4/2025 1 112 Finance 1/7/2025 1 112 Finance 1/8/2025 1 112 Finance 1/11/2025 1 112 Finance 1/12/2025 1 112 Finance 2/10/2025 1 113 IT 1/4/2025 1 113 IT 2/1/2025 1 114 IT 1/2/2025 1 114 IT 1/3/2025 1 114 IT 1/4/2025 1 120 Finance 1/2/2025 1 120 Finance 1/4/2025 1 120 Finance 2/1/2025 1 120 Finance 2/3/2025 1 132 Finance 1/2/2025 1 132 Finance 1/4/2025 1 132 Finance 1/5/2025 1 132 Finance 1/6/2025 1 132 Finance 2/3/2025 1 132 Finance 2/4/2025 1 132 Finance 2/5/2025 1 132 Finance 3/2/2025 1 132 Finance 3/3/2025 1 132 Finance 3/4/2025 1 I should see 2 employees for January and 1 for February for a total of 3 as highlighted in red above.
Here is the result from your measure:
- 6 months ago
Hi buttercream here ISINSCOPE handles the total row correctly by summing the monthly values instead of recalculating across all months. Sharing the PBIX file for reference.
- 6 months ago
buttercream
Assuming you have a period column the following DAX expression should give you what you're looking for:Finance Emp 3+ Leaves = VAR _Group = "FINANCE" VAR _Base = SUMMARIZE( CALCULATETABLE( 'Table', 'Table'[Group] = _Group ), [Employee], [Period] ) VAR _CheckLeave = FILTER( ADDCOLUMNS( _Base, "Leave", VAR _Emp = [Employee] VAR _Period = [Period] RETURN CALCULATE(SUM('Table'[Leave]), 'Table'[Employee] = _Emp, 'Table'[Period] = _Period) ), [Leave] > 3 ) RETURN COUNTROWS(_CheckLeave)I've attached a sample pbix for you.
hnguy71
6 months agoSuper User
buttercream
Assuming you have a period column the following DAX expression should give you what you're looking for:
Finance Emp 3+ Leaves =
VAR _Group = "FINANCE"
VAR _Base =
SUMMARIZE(
CALCULATETABLE(
'Table',
'Table'[Group] = _Group
),
[Employee], [Period]
)
VAR _CheckLeave =
FILTER(
ADDCOLUMNS(
_Base,
"Leave",
VAR _Emp = [Employee]
VAR _Period = [Period]
RETURN
CALCULATE(SUM('Table'[Leave]), 'Table'[Employee] = _Emp, 'Table'[Period] = _Period)
),
[Leave] > 3
)
RETURN
COUNTROWS(_CheckLeave)
I've attached a sample pbix for you.
buttercream
6 months agoHelper II
This works. Wow. Thank you.