Forum Discussion

buttercream's avatar
buttercream
Helper II
5 months ago
Solved

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...
  • buttercream's avatar
    buttercream
    5 months ago

    My apologies but I'm trying to calculate it by month.  Example below.

     

    EmployeeGroupDateLeave
    101Finance2/12/20251
    101Finance2/13/20251
    101Finance2/14/20251
    101Finance2/15/20251
    101Finance2/16/20251
    101Finance3/20/20251
    101Finance3/21/20251
    101Finance3/22/20251
    107IT1/22/20251
    107IT1/23/20251
    107IT1/24/20251
    107IT1/25/20251
    107IT2/3/20251
    107IT2/4/20251
    108IT2/1/20251
    108IT2/2/20251
    108IT2/3/20251
    108IT2/4/20251
    112Finance1/7/20251
    112Finance1/8/20251
    112Finance1/11/20251
    112Finance1/12/20251
    112Finance2/10/20251
    113IT1/4/20251
    113IT2/1/20251
    114IT1/2/20251
    114IT1/3/20251
    114IT1/4/20251
    120Finance1/2/20251
    120Finance1/4/20251
    120Finance2/1/20251
    120Finance2/3/20251
    132Finance1/2/20251
    132Finance1/4/20251
    132Finance1/5/20251
    132Finance1/6/20251
    132Finance2/3/20251
    132Finance2/4/20251
    132Finance2/5/20251
    132Finance3/2/20251
    132Finance3/3/20251
    132Finance3/4/20251

     

    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:

     

     

  • techies's avatar
    techies
    5 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.

     

     

  • hnguy71's avatar
    5 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.