Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
fabiocovre
Advocate I
Advocate I

Calculate Exposed Employees in a Period

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

10 REPLIES 10
v-ljerr-msft
Microsoft Employee
Microsoft Employee

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. Smiley Happy

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

R1.PNG

Regards

Hello v-ljerr-msft,

 

Thank you for your answer! It helped me a lot!

 

Another little question: I'd like to sum this mesasure, is it possible?

 

Thank you,

Fábio

Hi @fabiocovre,


Another little question: I'd like to sum this mesasure, is it possible?


Yes, the formula below is for your reference. Smiley Happy

Measure 2 = SUMX( Employees , [Measure] )

m2.PNG

 

Regards

Hey @v-ljerr-msft,

 

Almost there... I think this picture below represents what I need. I'd like to sum the number of exposed employees by month and then, be able to have differents views with this amount, for example, by sex, by age group etc...

 

You are helping me a lot. Thank you very much for your time!

 

EXPOSED.png

 

 

Hi @fabiocovre,

 

It seems that the sample data is now different from what you have shared in the original post. Could you share your current pbix file, so that I can further assist on the issue? Smiley Happy

 

Regards

Hey @v-ljerr-msft

 

Sorry, this is the original file. But the data structure is the same.

 

 

Sample

 

Thank you! Smiley Happy

 

 

Hi @fabiocovre,

 

How did you calculate the numbers 22.677 and 59.09?  Please be very clear.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

 

I'm sorry if I wasn't clear in my explanation.

 

So, in this example, my difficulty is to find the number 22.677 instead of 2990. This value is obtained by the cumulative sum of Measure2 monthly (3425 + 3321 +... + 3041).

 

The other value, 59,09, is easier... it's obtained by the total expenses in the period (1.340.026,29) divided by 22.677.

 

 

Sem título.png

To get until here, I used the formulas below that @v-ljerr-msft gave me.

 

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

 

Measure 2 = SUMX( Employees , [Measure] )

 

Thank you for your time and help!

 

Fabio

Hi @fabiocovre

 

Try this edited measure2 formula

 

=if(HASONEVALUE(Expenditures[PaymentMonth]),SUMX(Employees,[Measure]),SUMX(SUMMARIZE(ALLSELECTED(Expenditures[PaymentMonth]),[PaymentMonth],"ABCD",SUMX(Employees,[Measure])),[ABCD]))

Hope this helps.

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
fabiocovre
Advocate I
Advocate I

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.