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

[amitchandak]

@Avivek ,Can you share sample data and sample output in table format?

[AllisonKennedy]

You need to step back and think about the CONTEXT and what is actually happening in the report, and what you actually want to achieve.

For a start, you don't need the CALCULATE on this function:
Wt avg 2 = CALCULATE(SUMX(RECEIVABLES,RECEIVABLES[Days Diff]*RECEIVABLES[Weights]))

SUMX does all you need there.

Second, variables are calculated when they are defined, not when they are used.

Measures are calculated when they are used/referred to, and are calculated as though they have a nested CALCULATE function around them. See my post here for what this means: https://excelwithallison.blogspot.com/2020/06/dax-context-transition-why-it-can-be.html

Otherwise, see if this post can help you to get the right result at both levels of your data:
https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/