cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft 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
Microsoft 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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors