cancel
Showing results for
Did you mean:
Highlighted
Member

## Calculating rolling average without a date

Hello

I'm a little stuck and hope you can help?  I need to calculate a rolling average of ITIssues over a time series but the time series does not contain a date, just period numbers.  My example data is:

 Period Period Number Year Index ITIssues 2017/18P1 P1 2017/18 0 11 2017/18P2 P2 2017/18 1 15 2017/18P3 P3 2017/18 2 10 2017/18P4 P4 2017/18 3 15 2017/18P5 P5 2017/18 4 15 2017/18P6 P6 2017/18 5 11 2017/18P7 P7 2017/18 6 9 2017/18P8 P8 2017/18 7 6 2017/18P9 P9 2017/18 8 7 2017/18P10 P10 2017/18 9 8 2017/18P11 P11 2017/18 10 7 2017/18P12 P12 2017/18 11 9 2018/19P1 P1 2018/19 12 7 2018/19P2 P2 2018/19 13 12 2018/19P3 P3 2018/19 14 8 2018/19P4 P4 2018/19 15 11 2018/19P5 P5 2018/19 16 6 2018/19P6 P6 2018/19 17 9 2018/19P7 P7 2018/19 18 7 2018/19P8 P8 2018/19 19 10 2018/19P9 P9 2018/19 20 14 2018/19P10 P10 2018/19 21 9 2018/19P11 P11 2018/19 22 14 2018/19P12 P12 2018/19 23 7

The period numbers do not correspond to equal date periods eg period 1 in 2017/18 is not the same date as period 1 in 2018/19.  I need to see a rollling average over the periods.

Many thanks

Jo.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculating rolling average without a date

I would create a new calculated column that is essentially the numeric equivalent of your Period like:

201818

201819

...

Then you could use the same basic concept of a rolling average without the date. You should be able to do something like:

`Column = SUBSTITUTE([Column1],"/","") * 1`

This may also help:

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User

## Re: Calculating rolling average without a date

I would create a new calculated column that is essentially the numeric equivalent of your Period like:

201818

201819

...

Then you could use the same basic concept of a rolling average without the date. You should be able to do something like:

`Column = SUBSTITUTE([Column1],"/","") * 1`

This may also help:

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 37 members 1,015 guests
Recent signins: