Filtering daterange for averagex, sumx, minx, maxx and stdevx

[stephras]

Hi all,

I am working on a rather large dataset where I want to calculate measures across the entire dataset/daterange as well as the same measures just for the latest 3 months prior to a customers last transaction date.

I've got it working just fine for finding average monthly revenue by using averagex like so: 

And the same for minx, maxx, and stdevx across the entire daterange. All good and fine.

But when it comes to just looking at the last three months prior to a specific date I am unsure how to do it.

Here's my attempt so far for finding the max value:

In this case what it returns is simply the sum total for the three months specified, which is obviously not what I am looking for.

I suspect it has something to do with filter context, but I am still new to this.

Any help would be much appreciated 🙂 

[stephras · Accepted solution]

For anyone curious or with the same problem. It seems I found the solution with the help from this video (hope it's okay to post it).

https://www.youtube.com/watch?v=ACvYaXnpyCM

It was a matter of considering it as a rolling average og adjusting the "LastSelectedDate to correspond with my own "last transaction date". It may not be the most elegant solution, but it works for now. 

[PC2790]

Hey can you try something like this:

Measure = 
var TimeFrame = -90
var LastOrderedDate = LASTDATE(Orders[OrderDate])
return
CALCULATE(Sum([ColumnName]),DATESINPERIOD(DatesTable[Date],LastOrderedDate,TimeFrame,DAY))

[stephras]

Hi @PC2790 ,

Not sure that would accomplish what I want to.

I am looking to calculate the averagex, minx, maxx, stdevx on the monthly revenue. It works just fine when I don't try to filter by date. So basically i would want the "VALUES('Date'[Month]) to only contain last three months prior to my measure [last transaction date].... i guess 🤔

MAXX(
   VALUES('Date'[Month]), //  <-- should only contain last three months prior to [last transaction date]
      CALCULATE(AVERAGEX(SUM('poc vFact_SalesLine'[SalesLine_RevenueExVatDKK])
   )
)