Total is diffrent from the actual total (Measure)

[Mannai]

Hello 

I'm calculating the sum of leaves for the last 12 month for key people where the filter says that the employee will be counted only if the year of his leave date is the same where he is a key people (Note that an employee can be a key people in 2021 and not in 2022 so when he leaves the company in 2022 he will not be counted) 

when i perform this measure,

Total leaves KP = CALCULATE([Total leaves],Filter('Employees',YEAR('Employees'[leave_date]) = YEAR(MAX('Key People List'[Year]))),FILTER('Key People List','Key People List'[Type ]="KP"),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH)) 

it worked in the table when i calculate the rown manually but the measure shows another value  :

any solution ?

[v-rzhou-msft]

Hi @Mannai ,

I suggest you to try this code. 

Total leaves KP =
VAR _RANGEEND =
    EOMONTH ( TODAY (), -1 )
VAR _RANGESTART =
    EOMONTH ( TODAY (), -13 ) + 1
RETURN
    CALCULATE (
        [Total leaves],
        FILTER (
            'Employees',
            YEAR ( 'Employees'[leave_date] ) = YEAR ( MAX ( 'Key People List'[Year] ) )
        ),
        FILTER ( 'Key People List', 'Key People List'[Type] = "KP" ),
        FILTER ( 'Date', 'Date'[Date] >= _RANGESTART && 'Date'[Date] <= _RANGEEND )
    )

 My Sample:

Date = CALENDARAUTO()

Relationship:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mannai]

Thank for your reply
i used your formula but still have the wrong total still

i'm supposed to have 21 as a total but i'm getting 12 (Note that when i add the total manually i get 21)

[v-rzhou-msft]

Hi @Mannai ,

I think this should be based on your  data model. Could you share a sample file with same issue with me? And show me a screenshot with the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.