Forum Discussion

stephras's avatar
stephras
Frequent Visitor
4 years ago
Solved

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 🙂 

 

 

 

 

Need Help
  • stephras's avatar
    stephras
    4 years ago

    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. 

     

     

3 Replies

  • PC2790's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      stephras
      Frequent Visitor
      4 years ago

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

       

  • stephras's avatar
    stephras
    Frequent Visitor
    4 years ago

    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.