Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
stephras
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: 

stephras_0-1658912872524.png

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:

stephras_1-1658912954184.png

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

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. 

 

stephras_0-1658930553284.png

 

View solution in original post

3 REPLIES 3
stephras
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).

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. 

 

stephras_0-1658930553284.png

 

PC2790
Community Champion
Community Champion

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

 

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

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.