Training Hours Per Person This Year and Last Year

[nimdy]

I have three tables: 

1. Employees - with columns name, employee number, start date, leave date, type of employee

2. Training Records - a line for each training entry, columns including employee number, training date, number of hours the training lasts, training category

3. A date dimension table - columns including date, month, year etc. 

I would like to create a graph that shows the number of training hours per head, that is the count of distinct active employees (as the same employee could have had multiple training sessions in the system) divided by the sum of training hours. I would then like to do the same for the previous year so I can add both measures to the same graph. 

I have a measure that works out active employees, this seems to be working correctly. The issue I seem to be having is using the SAMEPERIODLASTYEAR function. When testing it with a table, the sum of last years training hours doesn't add up. Would someone be able to help?

[tex628]

Can you provide an image of the table along with the two dax measures you are using?

[nimdy]

Sure thing. This is the measure to calculate active employees (borrowed and amended from another community thread): 

Active Employees = 
VAR MaxDate = MAX ( 'MasterData dimDate'[Date] )

VAR EmpCnt =
    CALCULATE (
        COUNTROWS (
            CALCULATETABLE ( 'Employees1', Employees1[Start Date] <= MaxDate, ALL ( 'MasterData dimDate' ) )

                                ),
                              (ISBLANK ( 'Employees1'[Leave Date] ) || 'Employees1'[Leave Date] > MaxDate)
                       )
RETURN
IF ( ISBLANK ( EmpCnt ), 0, EmpCnt )

This is then the measure to calculate training hours per person:

TestingTrainingHoursPerHead = CALCULATE(DIVIDE(SUM(Training[Training Hours]),Employees1[Active Employees],0))

Which I then copied to a new measure, adding the SAMEPERIODLASTYEAR function:

TestingTrainingHoursPerHeadLY = CALCULATE(DIVIDE(sum(Training[Training Hours]),Employees1[Active Employees],0),SAMEPERIODLASTYEAR('MasterData dimDate'[Date]))

The above is a table created with dimDate.Date, the active employees measure and the two training hours measures (last year being the second one prefixed LY). There is currently a date slicer showing this year, so I know the 18.06 value is correct, but if I change the date slicer to last year the figure I get is 19.45 not 5.02. 

I think it has something to do with the slicer, or only part of the TestingTrainingHoursPerHeadLY measure looking back a year (as there's two parts to it - employee count, and sum of hours), but I have attempted to build a measure for these separately but still get the same figure. 

[tex628]

TestingTrainingHoursPerHeadLY = 
VAR period = SAMEPERIODLASTYEAR('MasterData dimDate'[Date])
Return
CALCULATE(
DIVIDE(sum(Training[Training Hours]),Employees1[Active Employees],0),
ALL('MasterData dimDate'),
period
)

Try this measure instead and tell me what result you get instead.

[nimdy]

Hi tex628. This gives me the same value unfortunately. 

[v-lili6-msft]

hi, @nimdy 

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

and if possible, could share your sample pbix for us have a test?

Best Regards,

Lin