Forum Discussion

fabiocovre's avatar
fabiocovre
Icon for Advocate I rankAdvocate I
9 years ago

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...
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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? :smileyhappy:

 

Regards

fabiocovre's avatar
fabiocovre
Icon for Advocate I rankAdvocate I
8 years ago

Hey v-ljerr-msft

 

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

 

 

Sample

 

Thank you! :smileyhappy:

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi fabiocovre,

     

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

    • fabiocovre's avatar
      fabiocovre
      Icon for Advocate I rankAdvocate I
      8 years ago

      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.

       

       

      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

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        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.