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.
