Last 6 month weighted average

[Avivek]

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.

[Anonymous]

Try this one:

// Last 6-Month Weighted Average

[Last 6M Wt Avg] =
var __lastDate = MAX( 'Date'[Calendar Date] )
var __veryFirstDateInCalendar =
    FIRSTDATE(
        ALL( 'Date'[Calendar Date] )
    )
// we have to check if there are enough days
// in the Date table to cover the 6Mth period
// starting from __lastDate (and going back).
// If not, we have to return BLANK().
var __boundaryDate = 
    DATEADD(
        __veryFirstDateInCalendar,
        +6,
        MONTH
    ) - 1
var __result =
    if( __boundaryDate <= __lastDate,
        var __period = 
            DATESINPERIOD(
                'Date'[Calendar Date],
                __lastDate,
                -6,
                MONTH
            )
        var __avg =
            CALCULATE(
                [Wt avg 2],
                __period
            )
        return
            __avg
    )
return
    __result

Of course, the Date table must be marked as such in the model and the unique date indentifier must be the 'Date'[Calendar Date] column. Since this table should contain at least 1 full year of dates, the code above works correctly.

[Anonymous]

1. Are you sure that [Wt avg 2] is correct? Have you confirmed this on many different periods of time?
2. Is the 'Date' table marked as the Date table in the model and correctly connected to the 'Receivables' fact table?
3. How did you check manually? When doing this manually, are you using exactly the same formulas as in the measure [Wt avg 2]?

[Avivek]

Thankyou @Anonymous , I madea small change, instead of using the calender date I used the payment recieved date and individually records are correct but when it shows as total its not the correct value. Below is the measure:

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

Last 6 months Wt Avg4 =

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

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

return

CALCULATE( [Wt avg 2],

FILTER('RECEIVABLES','RECEIVABLES'[Payment Recieved date]>= startdate && RECEIVABLES[Payment Recieved date]<= enddate))

Below is the sanp of the report and there we can see that the totals do not match with the value

Though the measure works it still gives inaccurate total.

[Anonymous]

@Avivek,

I understand that the field [Month & Year] is one taken from the DATE table? I think the problem you've got stems from the fact that you have hardcoded into your measure the dates through 'LAST REFRESHED'[Data Last Refreshed]. Since this is the same on all the rows in your visual, you don't get a 6-Month Weighted Average for the chunk of time that you've put on your rows but for the 6-Mth period that end on the last refresh date.

[amitchandak]

@Avivek , 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)
)

[Avivek]

@amitchandak , it  shows an error

It may seem a little silly, please don't mind as I am still at a beginner level and maybe I am missing some logic behind the syntax

[amitchandak]

@Avivek , try like 

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

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

[Avivek]

Hi @amitchandak

I made soome changes in the measure and changed it to payment received date instead of calendar date and although it gives the right individual value but the total is not correct

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

 

Last 6 months Wt Avg4 =

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

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

return

CALCULATE( [Wt avg 2],

FILTER('RECEIVABLES','RECEIVABLES'[Payment Recieved date]>= startdate && RECEIVABLES[Payment Recieved date]<= enddate))

 

 

 

Below is the sanp of the report and there we can see that the totals do not match with the value

 

 

 

Though the measure works it still gives inaccurate total.

[Avivek]

@amitchandak It gives same answer, the new measure I named it as Last 6 month Wt Avg3,

also in this pic if we see in month and year it should be only for 6 months buts its showing for all the months and year, ideally by the measure it should be for 07/2020, 06/2020, 05/2020, 04/2020, 03/2020, 02/2020.

So I am not able to figure it out what is it i am missing, can you please help me out here.