Forum Discussion
Average a measure returning a single max value
In your current DAX setup, the logic seems correct, but there may be an issue with the way the dates are being handled. If you select a date where there are no price records, the measure will return blank. If you select a date after a price record, it should still return the most recent price record.
The problem may be related to the relationship between your DimDate and Price_Table tables, or how the Price_Table[SELECTION_DATE_START] is being used in the calculation.
Here's a slightly modified version of your DAX measure which should handle dates correctly:
Latest Price =
CALCULATE(
MAX(Price_Table[LABOR_RATE]),
FILTER(
ALL(Price_Table[SELECTION_DATE_START]),
Price_Table[SELECTION_DATE_START] <= MAX(DimDate[Date])
)
)Average Latest Price =
AVERAGEX(
VALUES(Price_Table[CUSTOMER_NUMBER]),
[Latest Price]
)Ensure the data type of the SELECTION_DATE_START field is date. If the date field is text, it might lead to the wrong results due to lexicographic comparison rather than date comparison.
also validate that no other filter is applied and validate the relationship between dimdate and price table.
If my assistance helped you in any way, hit 👍
Hi rubayatyasmin , this works if I pick the specific date for the price entry. For example, if pricing is done on 4/1/2023, the measures return the expected result and the average is correct. If I pick 4/2/2023, the data is blank. If I swith to MonthYear, picking 04/2023 returns the value. If I pick 05/2023 the data is blank. I'm looking to return that price value as long as there is not a new price record more recent that <= currentdate.
Community ChampionIn your current DAX setup, the logic seems correct, but there may be an issue with the way the dates are being handled. If you select a date where there are no price records, the measure will return blank. If you select a date after a price record, it should still return the most recent price record.
The problem may be related to the relationship between your DimDate and Price_Table tables, or how the Price_Table[SELECTION_DATE_START] is being used in the calculation.
Here's a slightly modified version of your DAX measure which should handle dates correctly:
Latest Price =
CALCULATE(
MAX(Price_Table[LABOR_RATE]),
FILTER(
ALL(Price_Table[SELECTION_DATE_START]),
Price_Table[SELECTION_DATE_START] <= MAX(DimDate[Date])
)
)
Average Latest Price =
AVERAGEX(
VALUES(Price_Table[CUSTOMER_NUMBER]),
[Latest Price]
)
Ensure the data type of the SELECTION_DATE_START field is date. If the date field is text, it might lead to the wrong results due to lexicographic comparison rather than date comparison.
also validate that no other filter is applied and validate the relationship between dimdate and price table.
If my assistance helped you in any way, hit 👍
rubayatyasmin , thank you so much. That worked. Again, thank you.
- rubayatyasmin
Happy to help. Will appreciate some kudos. 👍