# How to calculate YTD Average based on a selected date.



## Mavericks334 (Oct 18, 2012)

Hi 

I have the below DAX measure, 

=calculate([Value_Corrected1],datesbetween(Dates[Date],Year_Period[Selected_Year_Start_Date],Year_Period[Selected_Month_End_Date]))

That is giving me the sum of headcount for the dates that i select. However i need to get the average of the headcount for the period selected.

Any idea how it oculd be calculated.

Regards,
Renato.


----------



## powerpivotpro (Oct 18, 2012)

Forgive me if I have misunderstood, but if your original measure [Value_Corrected1] had been defined as an AVERAGE() measure rather than a SUM(), this would give you what you wanted.


----------



## Mavericks334 (Oct 19, 2012)

The problem, is each dept has 3 headcount 4 , 40 ,25 it varies, when i do an average it gives me 6.4 , 7 where i need to get 1020 because of the total hc.


----------



## powerpivotpro (Oct 19, 2012)

Still don't precisely understand your question yet, but I have a hunch you might need to use the AVERAGEX() function.  Something like AVERAGEX(VALUES(Table[Dept]), Table[HeadCount]) as your base measure, then use that base measure in your CALCULATE() with DATESBETWEEN().

SUMX is covered here - AVERAGEX works the same way:

SUMX() – The 5-point palm, exploding fxn technique « PowerPivotPro


----------



## Mavericks334 (Oct 19, 2012)

My Raw data table consist of Dept Id's and the hc associated with them,  the dept table consists the dept id's and Functional unit aligned to It. What i am trying to acheive, is based on the functional unit to get an average head count. Each dept could have any number of people associated with it and functional headcount is the sum of each dept associated. When i try to get the average for two month or 3 months based on my date select, what it does is, sums the entire rows for that period and divides it by the count of rows, which gives me say 6.5, 7, instead of suming up the total rows and then taking an average.


----------



## powerpivotpro (Oct 19, 2012)

Is there a relationship between those two tables?


----------



## Mavericks334 (Oct 19, 2012)

Yes there is a relationship, using the dept id's


----------



## Mavericks334 (Oct 19, 2012)

Rob, Below is my Sample Data
*Date**Value**Heading2_Code*10/1/201163310ED10/1/2011173310FL10/1/2011333310HD10/1/2011243310PZ10/1/2011113310RC10/1/201103310SG10/1/201173310SH11/1/201163310ED11/1/2011173310FL11/1/2011333310HD11/1/2011253310PZ11/1/2011113310RC11/1/201103310SG11/1/201173310SH12/1/201163310ED12/1/2011183310FL12/1/2011333310HD12/1/2011253310PZ12/1/2011113310RC12/1/201103310SG12/1/201173310SH

<COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><TBODY>

</TBODY>
Row LabelsSum of Value10/1/20119811/1/20119912/1/2011100Grand Total297

<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY>

</TBODY>

If i select my data as nov then it must give me the average of (98,99) which 98.5. and similar for other months. That is why i am using sum 1st and then trying and average. to get the values. If i try a direct average it gives me on 14.14 for the above data, which is not the average headcount for all the 3 months.

Do let me know incase you need additional clarifications.


----------



## powerpivotpro (Oct 21, 2012)

Can you send me the workbook, or a workbook with sample data illustrating the problem?  Email address in my signature.


----------



## Mavericks334 (Oct 22, 2012)

Hi Rob,

I have sent you the details as requested.

Please check it out and let me know incase you need any more clarifications.

Regards,
Renato.


----------



## Mavericks334 (Oct 18, 2012)

Hi 

I have the below DAX measure, 

=calculate([Value_Corrected1],datesbetween(Dates[Date],Year_Period[Selected_Year_Start_Date],Year_Period[Selected_Month_End_Date]))

That is giving me the sum of headcount for the dates that i select. However i need to get the average of the headcount for the period selected.

Any idea how it oculd be calculated.

Regards,
Renato.


----------



## Mavericks334 (Oct 23, 2012)

I created an additional measure, which is

=calculate([Value_Corrected1],datesbetween(Dates[Date],Year_Period[Selected_Year_Month_Start_Date],Year_Period[Selected_Month_End_Date]))/[Selected_Fiscal_Period]

Where [Selected_Fiscal_Period] = Max[Fiscal_Period]. 

I am able to get the average like this.


----------

