Calculate Exposed Employees in a Period

Hi fabiocovre,

Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report. So you should create a measure instead. 

The formula below to create the measure is for your reference. :smileyhappy:

Measure = 
VAR minSelectedPaymentMonth =
    MIN ( Expenditures[PaymentMonth] )
VAR maxSelectedPaymentMonth =
    MAX ( Expenditures[PaymentMonth] )
VAR startDate =
    MAX ( Employees[StartDate] )
VAR endDate =
    MAX ( Employees[EndDate] )
RETURN
    IF (
        maxSelectedPaymentMonth >= startDate
            && maxSelectedPaymentMonth <= endDate
            && minSelectedPaymentMonth <= startDate,
        DATEDIFF ( startDate, maxSelectedPaymentMonth, MONTH )+1,
        IF (
            maxSelectedPaymentMonth >= startDate
                && maxSelectedPaymentMonth <= endDate
                && minSelectedPaymentMonth >= startDate,
            DATEDIFF ( minSelectedPaymentMonth, maxSelectedPaymentMonth, MONTH )+1,
            IF (
                maxSelectedPaymentMonth >= endDate
                    && minSelectedPaymentMonth <= startDate,
                DATEDIFF ( startDate, endDate, MONTH )+1,
                IF (
                    maxSelectedPaymentMonth >= endDate
                        && minSelectedPaymentMonth >= startDate
                        && minSelectedPaymentMonth <= endDate,
                    DATEDIFF ( minSelectedPaymentMonth, endDate, MONTH )+1
                )
            )
        )
    )
        + 0

Regards

Hello,

I have two databases: Employees and Expenditures. They are connected by an ID field.

The Employees database contains the StartDate and the Enddate in the company

The Expenses database contains the expenditures of the employees in the health plan and the payment month. Important: not all the Employess had Expenditures with the health plan.

I'd like to calculate the Expenditures Per Capita. To this became posible, I thought to create a Calculated Column on the Employes database. For example, if I filter the Payment Month from January/17 to July/17 and the employee started the company at November/16 and quited the company at March/17, the column would return the value 3. Because the employee was exposed from January/17 until March/17.

My main difficult is to make this filter became dynamic.

I attached a sample database with the expected values. I hope someone help me with my doubt.

Thank you in advance.

Sample

Fábio