Forum Discussion

Lisa_G's avatar
Lisa_G
Frequent Visitor
8 years ago

YTD from measure

Hi, I cannot calculate the YTD of the employee data. Firstly I calculated in a measure the sum of the employee every day:   EmployeeSum = CALCULATE (           Sum ( 'dbc DIM_Employee_Temporal_V...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Lisa_G,

I am still confused about your problem, you'd better share some sample table and list the expected result. And display where the issue come from?

Best Regards,
Angelia

Lisa_G's avatar
Lisa_G
Frequent Visitor
8 years ago

Hi Angelia v-huizhn-msft,

in the dimension employee I have this columns:

- Employee Number

- Entry Date

- Leaving Date

- Full Time Employment (FTE)

- ValidFrom

- ValidTo

 

Our goal is to calculate the revenue per sales FTE. For this I need the Number of employee per day cumulated and the revenue total YTD: p.e.

1/1/18 TUSD 5  - 5 employees

1/2/18 TUSD 7 - 10 employees

1/3/18 TUSD 13 - 16 employees

 

What I have now:

For the revenue I used DAX: TOTALYTD() -> it works.

For the Employee Number (FTE) per day I made the measure (see my first message) -> employees per day p.e.

1/1/18  - 5 employees

1/2/18 - 5 employees

1/3/18 - 6 employees

 

I need:

Now I need the employees cumulated:

1/1/18 - 5 employees

1/2/18 - 10 employees

1/3/18 - 16 employees

 

Can you help me?

 

Thanks,

Lisa

 

 

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

    Hi Lisa_G,

    "1/1/18  - 5 employees, 1/2/18 - 5 employees, 1/3/18 - 6 employees" the 5,5,6 employees are calculated by the measure you posted above, right? If it is, please create a Calendar date, build a relationship between Calendar table and dbc DIM_Employee_Temporal_VIEW table, then create a measure using the following formula.

    Year-to-Day = TOTALYTD([EmployeeSum],'Calendar'[DATE])

     
    Then create a table visual, select the Calendar[Date], [Year-to-Day] measure as value level, and check if it returns correct result.

    Best Regards,
    Angelia

    • Lisa_G's avatar
      Lisa_G
      Frequent Visitor
      8 years ago

      Hi v-huizhn-msft,

      it won´t work because I need the employee by country and in the extra table I havn´t this information. It would be great to solve the problem with a measure.

       

      Thanks,

      Lisa

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Lisa_G , have you got the solution to your problem? I also want YTD value of measure but it's not workin, tried everything no luck yet.