Three Month Average of a Measure based on current Filtered Month

[JM_nxgn]

Hey guys,

I'm trying to write a measure to calculate the average 'Amount Accepted' for the previous three months based on the filtered month. 

For example if the filter is currently set to 'January' and '2020', I want a measure which will calculate the average 'Amount Accepted' for the months of October, November and December 2019 dynamically. 

Moreover, I'm also looking to add the functionality which limits the number of days it averages based on the current day.

For example today is the 23rd of January 2020. The measure should dynamically consider the 1st-23rd day of the previous three months only when calculating the average. 

I currently have a measure called 'Accepted' which does a distinct count on a boolean column called 'IsAccepted'. So i assume the measure will be based on this.

The measure looks as follows:

Accepted = CALCULATE(DISTINCTCOUNT(Detail[Items]), FILTER(Detail, Detail[IsAccepted] = TRUE()))

I'm going to post an example of the tables I am working with:

Date:

3 Month Accepted =

VAR SelectedMonth = SELECTEDVALUE(Dates[MonthDiff])

VAR StartingMonth = (SelectedMonth - 4)

RETURN CALCULATE((AVERAGEX(MeasureTable, [Accepted]) / 3), FILTER(ALL(Dates), Dates[MonthDiff] > StartingMonth && Dates[MonthDiff] < SelectedMonth))

Thanks for your help, if you have any other questions please ask me :).

[amitchandak]

Try Like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,MONTH))  

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[JM_nxgn]

@amitchandak  

Hi Amit, this doesn't completely solve my issue. It also includes the current filtered month and doesn't filter each month based on month to day.

[JM_nxgn]

I have since found something that worked for me thanks :).