Average calculation

[Anonymous]

Hi,

I hope you can help me. I want to calculate average FTE per month.

I have made the following example, but I cannot figure out how to calculate the FTE Avg. In February it should be based on the average of January and February ((250+247)/2) = 248,5

Month	Actual	YTD	FTE Avg.
1	250,00	250,00	250,00
2	-        3,00	247,00	248,50
3	4,00	251,00	249,33
4	-        2,00	249,00	249,25
5	-        3,00	246,00	248,60
6	5,00	251,00	249,00
7	-        1,00	250,00	249,14
8	-	250,00	249,25
9	-        3,00	247,00	249,00
10	4,00	251,00	249,20
11	-        3,00	248,00	249,09
12	-        3,00	245,00	248,75

I have tried 

FTE Avg = CALCULATE( 'ActualData'[Actual] , DATESYTD('Calendar'[Date])) / max('Calendar'[Month])

But it doesn't add January and February together.

[Anonymous]

@Greg_Deckler  @mahoneypat  @AllisonKennedy Thanks for your replies so far.

It seemed so self explaining for me, but I also have all the background.

I have now created an example.

https://1drv.ms/u/s!AvAYB6UZVhl7tju8Hi2xwXY2Uvai?e=762fNw

Thanks in advance

[mahoneypat]

I looked at your pbix file.  The measure I suggested seems to be working correctly.  Are you still seeing an issue?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[AllisonKennedy]

@Anonymous
Lots of good solutions posted for you already, but this blog does a great job of explaining the how and why of exactly what I think you're trying to do: https://blog.enterprisedna.co/calculate-a-year-to-date-ytd-monthly-moving-average-in-power-bi/

[mahoneypat]

Please try this expression.

Avg FTE To Date =
VAR thismonth =
    MIN ( 'Calendar'[Month] )
RETURN
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Calendar'[Month] ),
            CALCULATE ( SUM ( 'ActualData'[Actual] ) )
        ),
        ALL ( 'Calendar'[Month] ),
        'Calendar'[Month] <= thismonth
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Greg_Deckler]

@Anonymous - 

FTE Avg Measure =
  VAR __Month = MAX('Table'[Month])
RETURN
  AVERAGEX(FILTER(ALL('Table'),[Month]<=__Month),[YTD])

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.