Forum Discussion

fabiocovre's avatar
fabiocovre
Icon for Advocate I rankAdvocate I
8 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
8 years ago

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

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

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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi fabiocovre,

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

    Yes, the formula below is for your reference. :smileyhappy:

    Measure 2 = SUMX( Employees , [Measure] )

     

    Regards

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

      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!

       

       

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 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