Forum Discussion
Need help creating a measure
- 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.
Hi buttercream please try this
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:
- techies6 months ago
Super User
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.
- buttercream6 months ago
Helper II
Brilliant. Thank you so much.