Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've looked all over but struggling with this DAX. What I need is the average Registrations of the previous 3 months NOT including the current month. My data source is at the day level and I've rolled it up to the month level below.
I have a measure called [Total Registrations] = SUM(Digital_sp_Rpt_Registrations[Registrations])
The average measure I created (obviously doesn't work) is the following =
3 Month Avg =
VAR period_end = FIRSTDATE(Dates[Date])
VAR period_start = DATEADD(period_end,-3,MONTH)
return
CALCULATE(AVERAGEX ( VALUES ( Dates[Month Year] ), [Total Registrations] ),
DATESBETWEEN(Dates[Date],period_start,period_end))
However, the above measure gives me the following:
In the example above "3 Month Avg" for Sep 2019 should be the average of Jun, Jul and Aug. It should be 33077, not 25015.
Any suggestions?
Solved! Go to Solution.
Thanks @kentyler for your reply. It didn't exactly answer my question but it got me thinking in regards to the dates I was using.
For September 2019 the period_start date was 6/1/2019 and the period_end date was 9/1/2019. Those were the values I was expecting to get back. But, I started playing around with the end date and changed it to 8/31. That eneded up being the issue.
Here is what finally worked for me:
Thanks @kentyler for your reply. It didn't exactly answer my question but it got me thinking in regards to the dates I was using.
For September 2019 the period_start date was 6/1/2019 and the period_end date was 9/1/2019. Those were the values I was expecting to get back. But, I started playing around with the end date and changed it to 8/31. That eneded up being the issue.
Here is what finally worked for me:
CALCULATE(AVERAGEX ( VALUES ( Dates[Month Year] ), [Total Registrations] ),
DATESBETWEEN(Dates[Date],period_start,period_end))
the evaluation context that is rolling up to the day level is not changed here... so the dates retrieved by DATESBETWEEN, is limited to the dates in the evaluation context for each row
you might have to all an ALL(Dates) as part the the filters for CALCULATE, which will removed any filters on the dates that are coming from the exisiting evaluation context for any given row
Help when you know. Ask when you don't!
did you try having the measure return period end, or period start, to see if they are the values you think they should be
Help when you know. Ask when you don't!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |