Measure - if no data -> take last available data to calculate average

[Anonymous]

Hello All!

I have time and again used information in this forum to solve my many problems with PowerBi but can't seem to find a solution this time, so I'm hoping you guys can help me.

I am working on a report that gives me average prices for products purchased. Say I have a product - bananas - which I bought 4 times in the month of January, twice in February and once in May.  So my data looks like this:

Product	Month	Unit Price
Bananas	January	15
Bananas	January	18
Bananas	January	11
Bananas	January	13
Bananas	February	12
Bananas	February	9
Bananas	May	8

What I need is a matrix and a chart that would tell me the average price for Bananas per month. So January = 14,25, February = 10,50, May= 8  - that I have no problem with. The problem is with months like March and April. To ensure continutity of data, I would like PowerBi to recognize there is no unit price data for March and April and if that happens, take the last data/last average that is available (February in this case). So my final averages table would like like this:

Average Price per month	January	February	March	April	May
Bananas	14,25	10,50	10,5	10,5	8

I've tried several combinations to achieve this but can't get the right result 😞

Can provide example pbix if needed. 

Thanks!

[amitchandak · Accepted solution]

@Anonymous , Please check the solution by @mh2587 First, if that can solve. But I doubt simple Avg will work here

You prefer have Date/Month/Year month table with one column as sortable year month or Month (YYYYMM or MM)

Then you can have formula like

calculate(lastnonblankvalue(Date[Year Month], Average(Table[Unit Price])), filter( all('Date'), 'Date'[Date] <= max('Date'[Date])))

In filter in place date you can use year month( YYYYMM) or  month (MM)

[Anonymous]

@amitchandak Sorry for a late reply but I played around with it a bit and your suggestions was of tremendous help. The formula finally works like I wanted it to. Big High Five for you Sir! Thanks 😊

[mh2587]

Measure = AVERAGE('Table'[Unit Price])

[Anonymous]

Thanks but as I said that's not the issue I'm struggling with