cancel
Showing results for
Did you mean:
Highlighted
Helper I

## 30-Day Average as bubble size

I am trying to create a 30-day average by entity that i'd like to use categorically with the intent to use it as a bubble size or in grouping for a historgram chart. When I try and create the measure using my date field, it seems the metric only works with the date field being used in the graph or chart. How can I get around this?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: 30-Day Average as bubble size

HI @rstover,

You can try to use below measure formula to calculate rolling 30day average:

Rolling 30day Average =
VAR currDate =
MAX ( Table[Date] )
RETURN
CALCULATE (
SUM ( Table[Oil Volume] ),
FILTER ( ALLSELECTED ( Table ), [Date] >= currDate - 30 && [Date] <= currDate ),
VALUES ( Table[Well] ),
VALUES ( Table[Battery] )
)

BTW, if you want to create a bin of rolling average range, you need to use the calculate column instead, measure formula not able to use as category/group to expand records. (calculate column/table not able dynamic interact with filter/slicers)

Calculate column version:

Rolling 30day Average =
CALCULATE (
SUM ( Table[Oil Volume] ),
FILTER (
ALLSELECTED ( Table ),
[Date]
>= EARLIER ( Table[Date] ) - 30
&& [Date] <= EARLIER ( Table[Date] )
&& Table[Well] = EARLIER ( Table[Well] )
&& Table[Battery] = EARLIER ( Table[Battery] )
)
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User IV

## Re: 30-Day Average as bubble size

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Helper I

## Re: 30-Day Average as bubble size

 Well Battery Date Oil Volume Well A Battery A 1/1/2020 1000 Well A Battery A 1/2/2020 1100 Well A Battery A ... ... Well A Battery A 3/24/2020 500 Well B Battery B 1/1/2020 700 Well B Battery B 1/2/2020 800 Well B Battery B ... Well B Battery B 3/24/2020 300

Essentially I have a data table like the above. I need to calculate a 30-day average. I tried using the quick measures but couldn't get the measure to report anything in a timeless visualization. My goal is to take the Battery name with their lat/longs and plot on ArcGIS the Batteries with their respective 30-day average as a point size.

I'd also like to utilize the measure in a histogram where I can bin the measure in different bands 0-100, 100-500, 500-1000, 1000-2000, 2000+ for example.

Super User IV

## Re: 30-Day Average as bubble size

You can try like with Date dimension

Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-30,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Community Support

## Re: 30-Day Average as bubble size

HI @rstover,

You can try to use below measure formula to calculate rolling 30day average:

Rolling 30day Average =
VAR currDate =
MAX ( Table[Date] )
RETURN
CALCULATE (
SUM ( Table[Oil Volume] ),
FILTER ( ALLSELECTED ( Table ), [Date] >= currDate - 30 && [Date] <= currDate ),
VALUES ( Table[Well] ),
VALUES ( Table[Battery] )
)

BTW, if you want to create a bin of rolling average range, you need to use the calculate column instead, measure formula not able to use as category/group to expand records. (calculate column/table not able dynamic interact with filter/slicers)

Calculate column version:

Rolling 30day Average =
CALCULATE (
SUM ( Table[Oil Volume] ),
FILTER (
ALLSELECTED ( Table ),
[Date]
>= EARLIER ( Table[Date] ) - 30
&& [Date] <= EARLIER ( Table[Date] )
&& Table[Well] = EARLIER ( Table[Well] )
&& Table[Battery] = EARLIER ( Table[Battery] )
)
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors