Forum Discussion

Mannai's avatar
Mannai
Icon for Helper I rankHelper I
4 years ago

Total is diffrent from the actual total (Measure)

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Mannai
    Icon for Helper I rankHelper I
    4 years ago

    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)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.