Forum Discussion

Bfaws's avatar
Bfaws
Icon for Helper III rankHelper III
6 years ago
Solved

Measure for rolling 12 month average

Hi,   I`m looking for a measure to calculate average Full time equivalent (FTE) over a 12 month rolling period.  I have a table that shows the sum of FTE for each month.  What I would like is an ad...
  • stevedep's avatar
    stevedep
    6 years ago

    I see,

    Rolling12month = 
var v_dates =  DATESINPERIOD( Rolling_Calendar_Lookup[Date]; MAX( Rolling_Calendar_Lookup[Date] ); -12;  MONTH )
return
AVERAGEX(v_dates;[sum FTE])

    Power BI file here.

     

    Hope it helps. 

    Kind regards,

    Steve. 

stevedep's avatar
stevedep
Icon for Memorable Member rankMemorable Member
6 years ago

Hi,

 

See;

Rolling12month = CALCULATE(    AVERAGE( 'Table'[Sum of FTE]);    DATESINPERIOD( 'Date'[Date]; MAX( 'Date'[Date] ); -12;  MONTH ))

Power BI file is here. 

 

Some tweaking might be needed because the RT needs to be calculated on an aggregate. Pls share detailed data in case this does not work. 

 

Kind regards,

 

Steve.  

  • Bfaws's avatar
    Bfaws
    Icon for Helper III rankHelper III
    6 years ago

    Hi Steve, 

     

    Thanks for the response. I have tried this but dont quite get the result.  This is probably my fault though as I think i have over-simplied the information provided.  I have set up a pbix file which mirrors the real file in terms of format of data loaded and model structure.  Can you please have another look.

     

    Much appreciated.

     

    Brendan

    https://1drv.ms/u/s!AtGISPtZ7ajKgQDjcO06sW8SzOPn?e=wHVvjS 

    • stevedep's avatar
      stevedep
      Icon for Memorable Member rankMemorable Member
      6 years ago

      I see,

      Rolling12month = 
var v_dates =  DATESINPERIOD( Rolling_Calendar_Lookup[Date]; MAX( Rolling_Calendar_Lookup[Date] ); -12;  MONTH )
return
AVERAGEX(v_dates;[sum FTE])

      Power BI file here.

       

      Hope it helps. 

      Kind regards,

      Steve. 

      • Bfaws's avatar
        Bfaws
        Icon for Helper III rankHelper III
        6 years ago

        Hi Steve, 

         

        Many thanks.  That's worked exactly as expected.

         

        Brendan