Rolling Average of Measure

[rawiswarden]

Hello,

How do I calculate a rolling average of a measure?  Or is this not possible.  I've tried everything I can find online.  The measure is a conditional weighted average.

FICO (Avg) = calculate(sumx(filter(Facts_Originations, Facts_Originations[PortStats.FICO] > 0), Facts_Originations[PortStats.FICO] * Facts_Originations[PortStats.DiscountedLoanAmount]) / sumx(filter(Facts_Originations, Facts_Originations[PortStats.FICO] > 0), Facts_Originations[PortStats.DiscountedLoanAmount]), userelationship(Facts_Originations[PortStats.BookedDate], Calendar_Dates[Date]))

How can I calculate the average of this over the past 6 months?  Everything I try either seems focused on calculated columns, columns, or produces results that do not make sense (like the 1 month average does not tie).  Maybe it is because I am new to DAX, but this has turned out to be much harder than it should be.

[amitchandak]

Can you share a pbix along with the example output

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

=CALCULATE([FICO (Avg)],DATESBETWEEN(Calendar_Dates[Date],EDATE(MIN(Calendar_Dates[Date]),-5),MAX(Calendar_Dates[Date])),userelationship(Facts_Originations[PortStats.BookedDate], Calendar_Dates[Date]))

Ensure that in your visual Year and Month are dragged from the Calendar_Dates table.

[rawiswarden]

Hello @Ashish_Mathur 

Thank you for the reply.  I was on my home computer attempting your solution and for whatever reason, it did not behave the same as it is behaving on my work computer.

I've never used EDATE before, so I had to read about the formula this morning.  When I modify your formula to use EOMONTH instead, then it works.  The problem with EDATE is that it does not go to the last day of the month but rather that month prior.  For example, EDATE (2/28/2019, -5) is 9/28/2018.  The same with EDATE (11/30/2019, -1) would give 10/30/2019, not 10/31/2019.

Since my rolling average is full calendar months, I switched it to EOMONTH and now it works.

Rolling Avg = CALCULATE([FICO (Avg)],DATESBETWEEN(Calendar_Dates[Date],EOMONTH(MIN(Calendar_Dates[Date]),-6)+1,MAX(Calendar_Dates[Date])),userelationship(Facts_Originations[PortStats.BookedDate], Calendar_Dates[Date]))

[Ashish_Mathur]

You are welcome.  Thank you for correcting my mistake.

[rawiswarden]

Thanks for the help @Ashish_Mathur , but that measure just calculates the same number and puts it in every time period (even time periods before when the data starts).  It also takes a long time to complete the calculation when added to the table

[Ashish_Mathur]

Hi,

Let's start from scratch.  Explain the business question, share some data and show the expected result.

[TomMartens]

Hey @rawiswarden ,

I would give this pattern a try

Rolling Fico AVG = 
var _thecurrentMonth = MAX(Calendar_Dates[Date])
var _theLastSixMonths = ...
return
AVERAGEX(
	_theLastSixMonths
	, [FICO (Avg)]
)

It depends how easily it is to determine the last six months from your calendar table.

Regards,

Tom

[rawiswarden]

Hello @TomMartens ,

My calendar table is below.  Is there anything I should add or how would I determine the month end?  I can't figure out how to define the second variable.   I've tried using eomonth and a combination of the DAX date functions.

Calendar_Dates =

ADDCOLUMNS (

CALENDAR (min(Dimension_PortStats[BookedDate]), MAX(Dimension_PortStats[BookedDate])),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"MonthYearLong", FORMAT([Date], "MMMM YYYY"),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "MonthYear", Format ([Date], "m-yy")

)

I tried this but the numbers do not tie to if I just average what is displayed in my calculator.  Am I missing something?

Rolling Fico AVG  =

var _thecurrentMonth = MAX(Calendar_Dates[Date])

return

var _theLastSixMonths = CALCULATETABLE(

Facts_Originations,

DATESBETWEEN (Calendar_Dates[Date], eomonth(_thecurrentMonth, -1)+1, _thecurrentMonth)

)

return

AVERAGEX(

    _theLastSixMonths

    , [FICO (Avg)]

)

[TomMartens]

Hey @rawiswarden 

As far as I understand you need the average of 6 months, as far as I see you iterate across the table FACT_Originations, this was not was I meant.

First I added a new calcualted column to the calendar table like so (please be aware, that it is references my fact table):

"Running Month Index" , 
    var YearStart = YEAR(MIN('Fact Sale'[Invoice Date Key]))
    return
    ((YEAR([Date]) - YearStart) * 12 ) + MONTH([Date])

This creates an index for each month. This allows to navigate between months using simple integer maths.

The measure below concatenates the last six months, just for checking if the "correct"months are selected.

test = 
var maxRunningMonthIndex = CALCULATE(MAX('Calendar_Dates'[Running Month Index]))
var t = 

    SUMMARIZE(
        FILTER(
            'Calendar_Dates'
            , 'Calendar_Dates'[Running Month Index] >= maxRunningMonthIndex - 5 && 'Calendar_Dates'[Running Month Index] <= maxRunningMonthIndex
        )
        , 'Calendar_Dates'[Running Month Index]
    )
    
return

CONCATENATEX(
    t
    , [Running Month Index]
    , " | "
)

This allows to created something like this:

Of course your measure would look like this:

Rolling FICO (Avg) = 
var maxRunningMonthIndex = CALCULATE(MAX('Calendar_Dates'[Running Month Index]))
var t = 
    SUMMARIZE(
        FILTER(
            'Calendar_Dates'
            , 'Calendar_Dates'[Running Month Index] >= maxRunningMonthIndex - 5 && 'Calendar_Dates'[Running Month Index] <= maxRunningMonthIndex
        )
        , 'Calendar_Dates'[Running Month Index]
    )
    
return

AVERAGEX(
    t
    , [FICO (Avg)]
)

Hopefully this is what you are looking for.

Regards,

Tom

[rawiswarden]

Hello @TomMartens 

Thanks for the help but I must be terrible at DAX.  I am going getting the correct data in the total