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
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 🙂
Solved! Go to 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.
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.
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])
)
)
User | Count |
---|---|
115 | |
94 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |