cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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 Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors