cancel
Showing results for
Did you mean:
Frequent Visitor

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

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 🙂

1 ACCEPTED SOLUTION
Frequent Visitor

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).

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.

3 REPLIES 3
Frequent Visitor

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).

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.

Super User

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))
``````

Frequent Visitor

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])
)
)``````