Measure needed for last 6 months weighted average

[Avivek]

I have created the weighted average which seems to work desirably but I need the weighted average for last 6 months, below are the measures.

Wt avg 2 = CALCULATE(SUMX(RECEIVABLES,RECEIVABLES[Days Diff]*RECEIVABLES[Weights]))

Last 6 months Wt Avg2 =

VAR enddate = EOMONTH('LAST REFRESHED'[Data Last Refreshed],-1)

VAR startdate = EOMONTH(enddate,-7)+1

VAR DateTable =

CALCULATETABLE(

'DATE',

FILTER('DATE','DATE'[Calendar Date]>= startdate && 'DATE'[Calendar Date]<enddate)

)

---------------------------------------------------------

VAR wtavg =

CALCULATE( [Wt avg 2],DateTable)

RETURN

wtavg

Although when I see this value by each day then it does not seem to mach but it gives me the correct value for the overall weighted average by every month.

So I created another  measure which gives the correct value by day wise but the total weighted average days monthwise is not correct. The measure is as;

Weighted Avg days = CALCULATE(SUM(RECEIVABLES[Days Diff])*[Weights],VALUES(ACCOUNT[Parent Account]))

Last 6 months Wt Avg =

VAR enddate = EOMONTH('LAST REFRESHED'[Data Last Refreshed],-1)

VAR startdate = EOMONTH(enddate,-6)+1

VAR DateTable =

CALCULATETABLE(

'DATE',

FILTER('DATE','DATE'[Calendar Date]>= startdate && 'DATE'[Calendar Date]<enddate)

)

---------------------------------------------------------

VAR wtavg =

CALCULATE( [Weighted Avg days],DateTable)

RETURN

wtavg

In both the cases values do not match to the total for last 6 month weighted average days.

Can anyone suggest me what is the mostake I am doing on this 6 month weighted average and what I may need to change.

[v-zhenbw-msft]

Hi @Avivek ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft · Accepted solution]

Hi @Avivek ,

Do you want to display the average of the past 6 months like the first column or the second column?

If you want to display value like the first column, you can refer the following measure.

Last 6 months Wt Avg = 
var _today = TODAY()
var _last6months = DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))
return
CALCULATE([Wt avg 2],FILTER('Table','Table'[Date]>=_last6months && 'Table'[Date]<=_today))

If you want to display value like the second column, you can refer the following measure.

Last 6 Months = 
var _today = TODAY()
var _last6months = DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))
var _sumvalue = CALCULATE(SUM('Table'[value]),FILTER(ALLSELECTED('Table'[Date]),'Table'[Date]>=_last6months && 'Table'[Date]<=_today))
var _IF = IF(MAX('Table'[Date])<=_today&&MAX('Table'[Date])>=_last6months,1,0)
return
IF(
    _IF=0,BLANK(),
    DIVIDE(_sumvalue,6))

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[amitchandak]

@Avivek , I saw you posted the same problem in the past. Can you share sample data and sample output in a table format?

[Avivek]

Yes @amitchandak , I did try to post this earlier to get a solution but could not find one.I am adding the snapshot of the table with the 6 month weighted average from my report

.