Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have the following measure that is calculating a rolling 6 month average however it includes any current month data and if an account does not have any current data it does not appear in my matrix table. I need the rolling 6 months to count back from the month prior to today, and keep the ability to average by the count of labels, some accounts may have less then 6 months of data.
My data:
Invoice date Invoice Total
10/14/2021 | $ 2,175.02 |
11/19/2021 | $ 2,909.67 |
12/20/2021 | $ 2,158.40 |
1/27/2022 | $ 2,906.29 |
2/16/2022 | $ 2,068.96 |
3/24/2022 | $ 2,097.71 |
4/15/2022 | $ 2,165.85 |
Current measure result is $2,384.48 the average of November to April, I need the measure result to be $2,386.01 the average of October to March.
Current Measure:
Moving AverageX =
calculate(
DimMeasures[Invoice Total],
DATESINPERIOD('Calendar'[Date],lastdate('Calendar'[Date]),-5,MONTH))
/(
Calculate(
DISTINCTCOUNT(BillingPeriod[Label]),
DATESINPERIOD('Calendar'[Date], lastdate('Calendar'[Date]),-5,MONTH)))
There is an existing relationship between Calendar and Invoice Batches which is the DimMeasures-Invoice Total.
I've read tons of posts on this but have not been able to get any of the answers provided to work. Please let me know if there is any additional data is needed. I would really appreciate the assistance.
Solved! Go to Solution.
@mcaldwell , Try like
Rolling 5 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-5,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-6,MONTH))
With a minor tweek I was able to get one of @amitchandak suggestions to work. Here is where I landed.
Thank you amitchandak for the reply, unfortunately I still couldn't get that to work. Further discussion today from "experts" working on this says we won't be able to get it to work since we have wonky billing periods and we cannot get the calendar to just look back to prior month.
Hi @mcaldwell ,
What do you mean by "since we have wonky billing periods and we cannot get the calendar to just look back to prior month"?
If you have a full calendar table, you could try using the time intelligence function.
Ave_Invoice_TimeIntelligence =
AVERAGEX (
DATESINPERIOD (
'Calendar'[Date],
EOMONTH ( MAX ( 'Calendar'[Date] ), -1 ),
-6,
MONTH
),
[Invoice Total]
)
If you can't get the correct value, maybe you can try to limit the date range directly.
Ave_Invoice =
VAR pre_month_end =
EOMONTH ( MAX ( 'Calendar'[Date] ), -1 )
RETURN
AVERAGEX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] <= pre_month_end
&& 'Calendar'[Date] > EOMONTH ( pre_month_end, -6 )
),
[Invoice Total]
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@mcaldwell , Try like
Rolling 5 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-5,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-6,MONTH))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
19 | |
13 |
User | Count |
---|---|
125 | |
37 | |
29 | |
28 | |
24 |