cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## YTD Rolling Average by Month

Hi all,

I am relatively new to PowerBI so still learning everyday! I am trying to create a rolling YTD Average Headcount for Turnover analysis.

Below is a sumarised table that shows monthly headcount and leavers.

What I would like to show is YTD;

• in January, we had 1360 employees
• in February, it should show (1360+1372)/2 = 1366
• in March, it should show (1360+1372+X)/3 =  XXXX

and so on for the rest of the year. PLEASE HELP!! I am really stuck and have tried the quick measure but it isn't working:

```2017 Monthly Headcount rolling average =
IF(
ISFILTERED('2017 Date'[Month]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('2017 Date'[Month].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'2017 Date'[Month].[Date],
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('2017 Date'),
'2017 Date'[Month].[Year],
'2017 Date'[Month].[QuarterNo],
'2017 Date'[Month].[Quarter],
'2017 Date'[Month].[MonthNo],
'2017 Date'[Month].[Month]
),
__DATE_PERIOD
),
CALCULATE(
ALL('2017 Date'[Month].[Day])
)
)
)```

I have then created a running total measure and plan on then creating a calculation that divides the above with the below to give a monthly turnover %.

1 ACCEPTED SOLUTION
Employee

HI @Anonymous

Here is a simple calculated measure that might be close

```Measure = TOTALYTD(
'2017 Date'[Month]
)
/ MAX('2017 Date'[Month Number])```

If you put it on a Table visual with your Month column you get the correct results.

Proud to be a Datanaut!

2 REPLIES 2
Employee

HI @Anonymous

Here is a simple calculated measure that might be close

```Measure = TOTALYTD(
'2017 Date'[Month]
)
/ MAX('2017 Date'[Month Number])```

If you put it on a Table visual with your Month column you get the correct results.

Proud to be a Datanaut!

Anonymous
Not applicable

Thank you so much @Phil_Seamark

I have one more question - I have slicers on my dashbaord which allow our Executives to filter the data for their Divisions/Groups but it seems this is no longer linked to teh Turnover visualisation - any ideas? Would this be because I sumarised the the table?

Thank you

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors