Rolling Average Invoice Cost Based on Number of Invoices

[PeterL1]

Hi, 

I'm struggling to find an answer to an issue that i have.

I am trying to get out the last 5 invoices by supplier down to an itemised level (by date), to calculate an average cost on the item for those last 5 invoices.

The Invoice dates ranks back to 2009, but i cannot just take invoices from say this year as i might not have bought something for 18 months etc.

I've also attached a link to some more Data.

https://www.dropbox.com/l/scl/AACHeYibKkWAnRxJTe5cW6aiJPgsZLUr6Dc

This is the Data i have:

 

This is the data i would like, by supplier and by item within that supplier.

Any help on this would be appreciated.

Thanks

Peter

[v-shex-msft]

Hi @PeterL1,

I cant access to sharing file, I think you need to check the settings.

You can follow below steps to if they suitable for your requirement:

1. Create table visual.

2. Add 'Supplier Id' and 'Item ID' column to table visual as group columns.

3. Add QTY and 'NET_VALUE' columns to visual with summary mode 'SUM'.

4. Write a measure to get average and drag to table visual.

AVERAGE =
DIVIDE ( SUM ( Table['NET_VALUE'] ), SUM ( TABLE[QTY] ), 0 )

If above not help, please share sample data for test.

Regards,

Xiaoxin Sheng

[PeterL1]

Hi @v-shex-msft

https://www.dropbox.com/s/t0hm8itfxd3bajf/Table1.xlsx?dl=0

I've attached another link, hopefully this one will work.

The calculation that you have written are fine for all the invoices, however i only want the last 5 invoices relating to each supplier and to ignore any previous invoice in the calculation (which is where i am struggling).

Thanks

Peter

[Zubair_Muhammad · Accepted solution]

@PeterL1

Try this MEASURE

Sum Value =
CALCULATE (
    SUM ( TableName[NET_VALUE] ),
    TOPN ( 5, TableName, TableName[INVOICE DATE], DESC )
)

[PeterL1]

Hi @Zubair_Muhammad

Thats it !

Thanks for the quick reply

Peter