Forum Discussion

Avivek's avatar
Avivek
Icon for Post Partisan rankPost Partisan
6 years ago

Last 6 month weighted average

I tried to create a measure of last 6 month weighted average days, the measure is as follows;

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

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( [Wt avg 2],DateTable)
 
RETURN
wtavg


But when I manually checked for individual accounts for the last 6 months and it does not match all.
Is there a problem with the measure and what needs to be corrected if so.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Avivek , seem correct, Try like

    Last 6 months Wt Avg =
    VAR enddate = EOMONTH('LAST REFRESHED'[Data Last Refreshed],-1)
    VAR startdate = EOMONTH(enddate,-6)+1

    VAR wtavg =
    CALCULATE( [Wt avg 2],
    FILTER('DATE','DATE'[Calendar Date]>= startdate && 'DATE'[Calendar Date]<= enddate)
    )