Calculate sum of distinct values in a data range

[Anonymous]

Hi,

I would like to calculate a FTE (full time equivalence), a measure to calculate the headcount :

HC-Headcount = IF (
[Data Date] >= MIN ( 'Calendar'[Date] ),
CALCULATE (
DISTINCTCOUNT ( 'xa BuildUp To Gross'[Payroll nb] ),
FILTER (
'xa BuildUp To Gross',
COUNTROWS (
FILTER (
RELATEDTABLE ( xADM00_General),
'xa BuildUp To Gross'[Administrative start date] <= MAX ( Calendar[Date] )
&& (
ISBLANK ( 'xa BuildUp To Gross'[Administrative End Date]) 
|| 'xa BuildUp To Gross'[Administrative End Date] >= MAX ( Calendar[Date] )
)
)
)
> 0
)
)
)

I’ve tried to use the same formula of headcount for FTE just changing the line : countrows for sum, sumx, but isnt work. 

for the headcount, each one has a FTE (full time equivalence), expressed in the column C.

[Greg_Deckler]

It's tough to tell exactly what you are trying to do here but I am guessing you want the FTE for a point in time taking into considering the start and end dates of employees? Have a look at my new measure in the Quick Measures gallery, made possible by @Phil_Seamark's fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence. The Quick Measure link is here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

Should be easily adaptable to your situation.

[Anonymous]

HI @Greg_Deckler, Thanks for your qucik reply. 

It's almost what I'm looking for, but your measure counts rows, and I'd like to sum the rows, I'tll try to be more clear: 

I have a payroll file that I've received every month, with some columns: 

- Reference: respective month and year 

- Payroll number : number of register of the employee 

- FTE : Full time equivalence 

- Work pattern: describing if the employee is part time or full time 

- Administrative start date : Hiring date, the date that starts the contract with the company 

- Administrative End date: Leving date, the date that finished the contract with the company 

In the powerbi database every month the employees record repeats itself until the month he/she left the company (If active the administrative End date is blank). Example: 

REFERENCE      PAYROLL NB         FTE         WORK PATTERN      ADM START DATE       ADM END DATE 

JAN 2017                 001                    0.6                 Part time                    10-01-2017 

JAN 2017                 002                    0.4                 Part time                    20-01-2017 

FEB 2017                  001                   0.6                  Part time                    10-01-2017 

FEB 2017                  002                   0.4                  Part time                    20-01-2017 

FEB 2017                  003                     1                   Full time                     01-02-2017 

MAR 2017                001                     1                   Full time                     10-01-2017 

MAR 2017                002                   0.4                  Part time                     20-01-2017 

MAR 2017                003                    1                    Full time                      01-02-2017 

...

MAR 2018                001                     1                   Full time                     10-01-2017 

MAR 2018                002                   0.4                  Part time                     20-01-2017 

MAR 2018                003                    1                    Full time                      01-02-2017

The troube/issue is: 

As headcount each month employee should counted as 1 ( it's sorted with the measure HC - Headcount as i've written above), but as FTE i need to sum the cvalues for FTE tto have the result: 

JAN 2017 Headcount is 2 and FTE is 1 

FEB 2017 Headcount is 3 and FTE is 2 

MAR 2017 Headcount is 3 and FTE is 2.4 

MAR 2018 Headcount is 3 and FTE 2.4 

As you can see depending on the month the FTE value can change, and i need to sum for each month the respective values. 

I have other tables with relationship to this one to use as filter, and a calendar table with no relation with the others. 

[Anonymous]

@Greg_Deckler

I've met a alternative way to make the sum, but I'm struglling to insert the filter for sum actives ones: 

FTE = IF (
    [Data Date] >= MIN ( 'Calendar'[Date] ), 
CALCULATE(Sum('xa BuildUp To Gross'[FTE]),USERELATIONSHIP('xa BuildUp To Gross'[Reference],'Calendar'[Date])))

[Anonymous · Accepted solution]

I've found the solucution : 

1. I've create a inative relation between the calendar Date and the reference date in the database

2. and use this measure: 

HC - FTE = CALCULATE (

    SUM ('xa BuildUp To Gross'[FTE]),
    FILTER (
        'xa BuildUp To Gross',
        'xa BuildUp To Gross'[Administrative start date] <= MAX ( Calendar[Date] )
                        && (
                            ISBLANK ( 'xa BuildUp To Gross'[Administrative End Date]) 
                                || 'xa BuildUp To Gross'[Administrative End Date] >= MAX ( Calendar[Date] ) )
    ),
USERELATIONSHIP('xa BuildUp To Gross'[Reference],'Calendar'[Date]))