Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
HI @Anonymous,
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
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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Very difficult to say. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
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...
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.
HI @Anonymous,
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
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |