YTD from measure

Question

Hi,I cannot calculate the YTD of the employee data.Firstly I calculated in a measure the sum of the employee every day:&nbsp;EmployeeSum = CALCULATE ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sum ( 'dbc DIM_Employee_Temporal_VIEW'[FTE] ); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;'dbc DIM_Employee_Temporal_VIEW'; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'dbc DIM_Employee_Temporal_VIEW'[ValidFrom].[Date] &lt;= MAX ( 'dbc DIM_Time_VIEW'[TimeDayID] ) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; 'dbc DIM_Employee_Temporal_VIEW'[ValidTo].[Date] &gt; MAX ( 'dbc DIM_Time_VIEW'[TimeDayID] ) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&amp;&amp; 'dbc DIM_Employee_Temporal_VIEW'[Entry Date].[Date] &lt; MAX ('dbc DIM_Time_VIEW'[TimeDayID] ) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&amp;&amp; ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; year('dbc DIM_Employee_Temporal_VIEW'[Leaving Date]) = 0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; || &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;'dbc DIM_Employee_Temporal_VIEW'[Leaving Date] &gt; MAX ('dbc DIM_Time_VIEW'[TimeDayID]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ))&nbsp;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.&nbsp;Thanks for your help.&nbsp;Lisa

v-huizhn-msft · Answer

Hi&nbsp;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 · Answer

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&nbsp;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&nbsp; - 5 employees1/2/18 TUSD 7 - 10 employees1/3/18 TUSD 13 - 16 employees&nbsp;What I have now:For the revenue I used DAX: TOTALYTD() -&gt; it works.For the Employee Number (FTE) per day I made the measure (see my first message) -&gt; employees per day p.e.1/1/18&nbsp; - 5 employees1/2/18 - 5 employees1/3/18 - 6 employees&nbsp;I need:Now I need the employees cumulated:1/1/18&nbsp;- 5 employees1/2/18&nbsp;- 10 employees1/3/18 - 16 employees&nbsp;Can you help me?&nbsp;Thanks,Lisa&nbsp;&nbsp;

v-huizhn-msft · Answer

Hi&nbsp;Lisa_G,
"1/1/18&nbsp; - 5 employees,&nbsp;1/2/18 - 5 employees,&nbsp;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&nbsp;dbc DIM_Employee_Temporal_VIEW table, then create a measure using the following formula.
Year-to-Day = TOTALYTD([EmployeeSum],'Calendar'[DATE])

&nbsp;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 · Answer

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.&nbsp;Thanks,Lisa

anonymous · Answer

Hi&nbsp;Lisa_G&nbsp;, 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.

Forum Discussion

YTD from measure

5 Replies

Related content

Previous YTD measure

YTD and YTD PY for multiple measures

YTD measure missing month?

YTD Measure

Measure for YoY YTD