YTD from measure

[Lisa_G]

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_VIEW'[FTE] );    

     FILTER (        

                'dbc DIM_Employee_Temporal_VIEW';        

               'dbc DIM_Employee_Temporal_VIEW'[ValidFrom].[Date] <= MAX ( 'dbc DIM_Time_VIEW'[TimeDayID] )        

                && 'dbc DIM_Employee_Temporal_VIEW'[ValidTo].[Date] > MAX ( 'dbc DIM_Time_VIEW'[TimeDayID] )        

               && 'dbc DIM_Employee_Temporal_VIEW'[Entry Date].[Date] < MAX ('dbc DIM_Time_VIEW'[TimeDayID] )        

               && (            

                     year('dbc DIM_Employee_Temporal_VIEW'[Leaving Date]) = 0           

                    ||            

                    'dbc DIM_Employee_Temporal_VIEW'[Leaving Date] > MAX ('dbc DIM_Time_VIEW'[TimeDayID])           

                      )    

                )

)

It gives me the right number for every day.

Secondly I need the YTD of the measure for other calculations. DAX like TOTALYTD doesn´t work.

Thanks for your help.

Lisa

[v-huizhn-msft]

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]

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]

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]

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]

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.