Hello friends ! I have a purchase history (about 100 records of each item), and I want to get the average cost of an item from the last month, it also serves me the average of the last 15 purchases … how could I get this?
Just in case you wish to have formula for last 30 days, please use something like following
=AVERAGE(SELECT(PurchaseHistory[Unit Cost], AND([Item] = “Apples”,[Shopping Date]>Today()-30)))
Please explore following expression.
You can compute average cost as
=AVERAGE(SELECT(PurchaseHistory[Unit Cost], AND([Item] = [_THISROW].[Item],[Shopping Month V]=MONTH(Today())-1)))
Where PurchaseHistory is the table where you have logged in your all purchases.
[Unit Cost] is the
unit cost of each item each time you have purchased.
[Shopping Month V] is a virtual column that computes the month when you have done shopping .
Please compute [Shopping Month V] as
[Shopping Month V] =Month([Shopping Date]) where[Shopping Date] is the date of shopping the item.
[Item] contains item names.
If you do have total cost and quantity instead of Unit Cost, then the expression will need some more additional part.
Hope this helps.
Thx Suvrutt, very usefull, i use it but… at the begining of the month
it dosent work… it will be better if
use the 30 latest records to calculate, any idea ?
Can you please clarify,when you mean last month, do you mean last 30 days from today? So , for example on 10th March, should the app
show average for 11th Feb to 9th March?
The expression I mentioned in my previous post will work for last calendar month.
In this case on any day in March from 1st to 31st March , it will show average for 1 to 28th February.
Appreciate if you can update how you wish to consider month and we can accordingly modify the expression.
Ok, it work better to me if it calculate last 30 purcharse from today (there are days with many purcharse, to diferentes providers)… thx