Forum Discussion
Helper ICalculating 30 days average based on selected date range in date slicer
Dear All,
I would like to calculate the rolling average on per 30 days basis based on the date selected in the data slicer (e..g last 3 months), currently I can display the average on calendar month basis in the matrix and graphs, would appreciate if you can share the right formula for calculating the average on 30 days basis rather than calendar month.
Thanks.
Br,
Choo
ckhoo2007 , you need measure bucketing
you need to create a measure
days = datediff(max(table[date]), selecteddate(Date[Date]), day)
or
days =
var _max = maxx(allselected(Date) , Date[Date])
return datediff(max(table[date]), _max, day)
then create an independent table with min, max and bucket
0 , 30, " 0 -30 days"
add others to the table. Use enter data
You need to use this table in new measures, where you need a group level
refer my blog or view for steps
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k
4 Replies
- amitchandak
Super User
ckhoo2007 , based on what I got.
If you need the last 30 days cumulative data , you can try a measure like an example
Sum by /30
Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-30,Day))/30
or
Rolling average 30 day = CALCULATE(average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-30,Day))
or
measure = averagex(values(Date[Date]), Sales[Sales Amount])
Rolling average 30 day = CALCULATE([measure],DATESINPERIOD('Date'[Date],max('Date'[Date]),-30,Day))
ckhoo2007Thanks Amit, yes I can get the rolling average now.
Sorry I wasn't explaining the situation clear enough in my post, what I am looking at is displaying 3 values i.e. average of 1-30 days, 31-60 days and 61-90 days? I can take the rolling average above on 30,60 and 90 days but how to put that dimension in the graph/ matrix where I can only select date or month?
Br,
Choo
- amitchandak
ckhoo2007 , you need measure bucketing
you need to create a measure
days = datediff(max(table[date]), selecteddate(Date[Date]), day)
or
days =
var _max = maxx(allselected(Date) , Date[Date])
return datediff(max(table[date]), _max, day)
then create an independent table with min, max and bucket
0 , 30, " 0 -30 days"
add others to the table. Use enter data
You need to use this table in new measures, where you need a group level
refer my blog or view for steps
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k