Rolling 30 days for each day for a selected time frame

[Avishekd1991]

Hi All...

I am struggling to create an average rolling 30 days Sales KPI for everydate of a selected time frame..

My usecase is:

User will select a date from the date slicer at the top...User will select MTD from the MTD/YTD toggle...and the x-axis on my bar chart will just show the relevant days applicable for that selected date. E.g: if user selects 07/09/2022...my x-axis should have dates from 1st of september to 7th of september.

The rolling 30 kpi...should work in the below way:

Bar on 1st Sep should show Avg Sales of 1st Aug till 1st Sept...

Bar on 2nd Sep should show Avg Sales of 2nd Aug till 2nd Sept...

.

.

.

Bar on 7th Sept should show Avg Sales of 7th Aug till 7th Sept...

Now when user selects YTD...

my X- axis now shows Months instead of days..

so for the selected day above, it should have Jan - Sept (till 7th)...and the rolling 30 should work in a way 

for Jan - Avg of 1st Jan to 31st jan...feb Avg of 1st to 28th...etc etc...for Sept it should show Avg of 1st sept to 7th Sept...

MTD selection is of more priority..

Thanks in advance

[lbendlin]

You can use either EDATE or WINDOW for this.  Note that "Rolling 30" is different from what you describe. For example 7th August to 7th September is 32 days.