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.
Good Day
Below is a screenshot of my query.
This is currently calculating ave sales for June - November.
However, November is not a "completed" month yet, therefore I would prefer to work out the average sales of the last "completed" 6 months (May - Oct.)
How can I amend my query? 🙂
Solved! Go to Solution.
Hi,
Averagex (
summarize (
calculatetable (
'Calendar' ,
Datesbetween (
'Calendar'[Date] ,
edate ( eomonth ( today (),- 1 )+ 1 ,- 5 ),
eomonth ( today (),- 1 ))), 'calendar'[Year] ,
'calendar'[Month name] ,
"ABCD" ,
[Sum of Inv+Order_Quantity]) ,
[ABCD]
)
My assumption is that Sum of Inv+Order_Quantity is a single measure.
You are amazing. Thank you very much. There's just a slight variance.
The average should be 263 266 but the formula above gived me 259 018.
It's not linked to the data slicer either. Any chance you know what is causing this variance?
Hi @bvanderwatt ,
Not very clear. Did @Ashish_Mathur 's method work for you ?
Best Regards,
Eyelyn Qin
Hi. The solution didn't work. Did you imply that I was unclear? If so, I will explain better if needed. 🙂
Hi @bvanderwatt
Please refer below solutions.
Ensure you have your 'Calendar' table joined on [created date]
Add the below measure:
Average6M:= CALCULATE ( AVERAGE( 'tableName'[columnName] ), DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -6, MONTH ) )
Regards,
Juventus
Hi. The last day in my calendar won't neccesarily be the today because I might have orders in the system to be dispatched in the upcoming months. Therefore, I don't think last date in the calendar table will work. 😞
Hi,
Try these measures
Quantity invoiced = SUM('Combined Sales'[QtyInvoiced])
Quantity invoiced in last 6 months = averagex(summarize(calculatetable('calendar',datesbetween('calendar'[date],edate(eomonth(today(),-1)+1,-5),eomonth(today(),-1))),'calendar'[Year],'calendar'[Month name],"ABCD",[Quantity invoiced]),[ABCD])
Hope this helps.
Hi. What does the "ABCD" stand for?
Hi,
It is the title of the virtual column which holds values of the measure "Quantity invoiced". Does my solution work?
Hi. I am sorry, but I don't understand the "ABCD" virtual column.
My error is starting at the "ABCD."
Hi,
Do not close the bracked after 'Calendar'[Month Name]. After ABCD should be the measure. There should be a closing bracket after the mesure.
Hi,
Averagex (
summarize (
calculatetable (
'Calendar' ,
Datesbetween (
'Calendar'[Date] ,
edate ( eomonth ( today (),- 1 )+ 1 ,- 5 ),
eomonth ( today (),- 1 ))), 'calendar'[Year] ,
'calendar'[Month name] ,
"ABCD" ,
[Sum of Inv+Order_Quantity]) ,
[ABCD]
)
My assumption is that Sum of Inv+Order_Quantity is a single measure.
You are amazing. Thank you very much. There's just a slight variance.
The average should be 263 266 but the formula above gived me 259 018.
It's not linked to the data slicer either. Any chance you know what is causing this variance?
Thank you. The figure of 259,018 is absolutely correct. That is average from October to Feb.
WOW! You are amazing. I amended the "5" to "6" and it worked perfectly. Thank you so much. I really appreciate all your help and advise. Thank you for taking the time to help me.
You are welcome. Thank you for your kind words.
Good Day Ashish aka GENIUS 🙂
Hoep you're very well.
I'm hoping you could point me in the right direction.
The below measure is the one we worked on together.
However, it's skipping months that had no sales. How could I amend the formula to include the empty months. It's skewing my results.
I tried SumX instead of AverageX, but must be doing something wrong.
Hoping you could save the day once again. 🙂
Thank you for your kind words. I am just a leaner. Share some data, describe the question and show the expected result.
I've created a new post. 🙂
Thank you so much for always helping me. I can't attach the PowerBi File. Can I email it to you?
Ave Qty for last 12 months = 871
However when I look at the ave per branch and sum it, it doesnt equal 871. I think it's because of the blank months.
You are welcome.
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |