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

     

     

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