Forum Discussion
Table/Measure Help LASTNONBLANK?
Hi Jamey
To calculate the beginning-of-year price (last valid price before the selected date range) for each vendor and material, you can use the following DAX measure:
Measure to Get the Last Price Before Date Range:
Beginning of Year Price = VAR MinDate = CALCULATE(MIN(Dates[Date]), ALLSELECTED(Dates)) RETURN CALCULATE( MAX(FCI_RAWMAT_PRICE_HIST[PRICE_AMT]), FCI_RAWMAT_PRICE_HIST[BEG_DATE] <= MinDate, FCI_RAWMAT_PRICE_HIST[END_DATE] >= MinDate, REMOVEFILTERS(Dates) -- Ignore any date filters for accurate calculation )- Add this measure to your table or matrix.
- It will display the price valid at the start of the selected date range for each vendor and product.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
Hi Jamey
To calculate the beginning-of-year price (last valid price before the selected date range) for each vendor and material, you can use the following DAX measure:
Measure to Get the Last Price Before Date Range:
Beginning of Year Price =
VAR MinDate = CALCULATE(MIN(Dates[Date]), ALLSELECTED(Dates))
RETURN
CALCULATE(
MAX(FCI_RAWMAT_PRICE_HIST[PRICE_AMT]),
FCI_RAWMAT_PRICE_HIST[BEG_DATE] <= MinDate,
FCI_RAWMAT_PRICE_HIST[END_DATE] >= MinDate,
REMOVEFILTERS(Dates) -- Ignore any date filters for accurate calculation
)
- Add this measure to your table or matrix.
- It will display the price valid at the start of the selected date range for each vendor and product.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS