Forum Discussion

ckhoo2007's avatar
ckhoo2007
Icon for Helper I rankHelper I
5 years ago
Solved

Calculating 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...
Need Help
ckhoo2007's avatar
ckhoo2007
Icon for Helper I rankHelper I
5 years ago

Thanks 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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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

  • ckhoo2007's avatar
    ckhoo2007
    Icon for Helper I rankHelper I
    5 years ago

    Thanks Amit!