Measure not working for row context

[Anonymous]

Hello

I am working on a HR report.  There are a few measures which I need to calculate.

I have a fact table from SQL which consists of a date for every employee for which they worked and a flag saying they were an employee that day. i.e.

There are some other feilds which are used for other measures and they are not included above.

The first measure is the number of active employees at a point in time.  For this I am using a modified version of some code I found on the internet

Active Employees = 
var currentdate = 
    max( FactDates[Date] )
Return
    CALCULATE (
        COUNTROWS ( factdates ),
        FILTER (
            factdates,
            ( FactDates[Date] = currentdate
                    && FactDates[ActiveEmployee] =1
                )
        )
    )

This returns the number of employees who were employeed at the end of each period this measure is used in.  If the chart/tables has a month grouping it will show the employees at the end of each month, if the group is daily it will show the employees for that day etc.  This works perfectly.

The second measure is the number of employees employeed at the end of the previous year.  For this I am using this code

Active Employees Last Year = 
var currentdate = 
    max(FactDates[Date-1Year])
Return
    CALCULATE (
        COUNTROWS ( FactDates ),
        FILTER (
            FactDates,
            ( FactDates[Date] = currentdate
                  && FactDates[ActiveEmployee] =1
                )
        )
    )

This will work as a total, but does not adjust for any date context that is added into the charts or table.

  

Any ideas why this is?

[v-cherch-msft]

Hi @Anonymous

You may try below measure and use the Date context. If it is not your case,please show us the simplified sample data or the pbix file.

Active Employees Last Year = 
var currentdate = 
    DATE(YEAR(max(FactDates[Date]))-1,MONTH(max(FactDates[Date])),DAY(max(FactDates[Date])))
Return
    CALCULATE (
        COUNTROWS ( FactDates ),
        FILTER (
           ALL(FactDates),
            ( FactDates[Date-1Year] = currentdate
                  && FactDates[ActiveEmployee] =1
                )
        )
    )

Regards,

Cherie

[AlB]

Hi @Anonymous

I think it might have to do with using both 'Date' and 'Date-1 Year' in the rows of the matrix, since they interfere with each other.

Can you show the structure of your table? Or share the pbix?