cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jojop1972 Member
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:

 

PeriodPeriod NumberYearIndexITIssues
2017/18

P1
P12017/18011
2017/18

P2
P22017/18115
2017/18

P3
P32017/18210
2017/18

P4
P42017/18315
2017/18

P5
P52017/18415
2017/18

P6
P62017/18511
2017/18

P7
P72017/1869
2017/18

P8
P82017/1876
2017/18

P9
P92017/1887
2017/18

P10
P102017/1898
2017/18

P11
P112017/18107
2017/18

P12
P122017/18119
2018/19

P1
P12018/19127
2018/19

P2
P22018/191312
2018/19

P3
P32018/19148
2018/19

P4
P42018/191511
2018/19

P5
P52018/19166
2018/19

P6
P62018/19179
2018/19

P7
P72018/19187
2018/19

P8
P82018/191910
2018/19

P9
P92018/192014
2018/19

P10
P102018/19219
2018/19

P11
P112018/192214
2018/19

P12
P122018/1923

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

1 REPLY 1
Super User
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!

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 37 members 1,015 guests
Please welcome our newest community members: