Hello friends ! I have a purchase history (ab...

[Efren]

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?

[Suvrutt_Gurjar]

hi @Efren,

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)))

[Suvrutt_Gurjar]

Hi @Efren,

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.

[Efren]

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 ?

[Suvrutt_Gurjar]

Hi @Efren,

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.

[Efren]

Ok, it work better to me if it calculate last 30 purcharse from today (there are days with many purcharse, to diferentes providers)… thx

[Efren]

Thx !