# MAX, MIN and AVERAGE handling no value

HI DNA ,

I have to calculate the MAX, MIN and Average within a date range of 14 days.
So in other words i have to dynamically look at all competitor prices for the last 14 days and determine, what the max price was withing these 14 days.

When i have the MAX price for all competitors i have to calculate what the overall MIN price is of all the MAX’ and the AVERAGE price of all the MAX’s.
MAX has to calculate a MAX price for all competitors and MIN and AVG have to give me an overall MIN and AVG across all MAX’ created earlier.

It should look like this:

My calculations for MAX is this:

Max price column =
CALCULATE(
MAX(competitor_v2[CompetitorPrice]),
DATESINPERIOD(‘calendar’[Date], LASTDATE(‘calendar’[Date]), -14, DAY),
ALLEXCEPT(competitor_v2, competitor_v2[CompetitorName], competitor_v2[ProductID])
)

MIN:

Min price measure =
CALCULATE(
MIN(competitor_v2[Max price column])
)

avg price measure =
CALCULATE(
AVERAGE(competitor_v2[Max price column])
)

It works okay, but the problem is that when calculating the min and average it includes the value of the rows that doesn’t have any values (In the above picture i’ve made the cell red) .

This means that everytime there’s a blank cell the calculation gives me the wrong numbers.

In the above picture, the circled date and data should be 159,98 as the day before and after, but since competitor 3 is missing a value on the 15. of October my calculation gives me the wrong value.

There’s not blank values in the data, but it’s the way DAX handles rows and columns that breaks my calculation.
So my question is:

How can fix either the MAX or the MIN and AVERAGE measures so that they either don’t include the rows that doesn’t excist? or how can i manipulate a MAX calculation, so that when there’s a rows that doesn’t have any value, it puts in the value of the day before or excludes the cell from the calculations?

Unfortunatly i cannot share the PBIX since the data is confidential.

I hope it makes sense.

You may try like this :
AVERAGEX ( FILTER ( Tablename, Tablename[Value] <> BLANK () ), Tablename[Value] )

Hi Uma,
Unfortunatly it doesn’t work since the value in the data set is not BLANK() but missing.

There’s no row for competitor 3 on the 15 of october, so i either have to ignore this in some way, when doing average or fill out the missing row some way?

I’ve been looking into creating a new table with data from my main table and filling out the missing data with a 0, but i can’t get this to work either.

Hi @jacob.thinggaard - Request to share the sample PBIX file for me to check further.

Hello @jacob.thinggaard, it’s been a while since we got a response from you.

Just following up if you still need help with your inquiry?

If you do, kindly provide the information the experts requested above so they can help you further.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

JTHI_Matas dynamic pricing.pbix (11.2 MB)

Hi i’ve attached the PBIX

Bumping this post for more visibility.

Hi @jacob.thinggaard! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!