FYTD Average calculation

[rpmitchell]

Hello,

I am stumped on calculating a FYTD Average. It seems like it would be such a simple thing, but I can't get it to work. When I'm reporting FTEs (Full-time Equivelants), I obviously can't add the separate months, so I'm trying to get an average. So for example, if there are 5 FTEs each Month, July-Dec, then the FYTD average is 5. The below formula works fine as long as I put the Months in the Report Filter section, and multiselect July-Dec, but it doesn't work when I put the months in columns. When the months are in columns, the formula gives a FYTD sum, not average. I want the formula to work both ways,whether I put the months in the Report filter or in columns. Any ideas on how to make this happen? Below is the formula.

[TABLE="width: 175"]
<tbody>[TR]
[TD]Actual Units FYTD:=[/TD]
[/TR]
[TR]
[TD]CALCULATE([Actual Units],DATESYTD(Calendar[Date],"06/30"))

And then to get it to turn into an Average, I use this:
[TABLE="width: 175"]
<tbody>[TR]
[TD]FYTD Avg:=IF(Countrows(Values(Calendar[MonthName]))>0,[/TD]
[/TR]
[TR]
[TD]Data[Actual Units FYTD][/TD]
[/TR]
[TR]
[TD]/CALCULATE(Countrows(Values(Calendar[MonthName]))),BLANK())

[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[scottsen]

CALCULATE(Countrows(Values(Calendar[MonthName])))

Not sure why you have the CALCULATE there (not needed, but shouldn't hurt), but I think the real problem is this is going to return just 1 row when you have just 1 month in the filter context.

I would think CALCULATE( Countrows(Values(Calendar[MonthName])) , DATESYTD(Calendar[Date]) ) might give you what you need?

 

[rpmitchell]

Yes, this works both ways. I see now that it was the DATESYTD function in the denominator that was missing from my formula. Thanks so much for your help!!