Forum Discussion
Table/Measure Help LASTNONBLANK?
I have a dataset that contains pricing for material, per vendor, and the date range these prices where valid. My goal is to show the price per month and if it changed (price variance). My date slicer is set for all data for this year, however I need to show what that price was at the beinging of the year, so I'm trying to figure out how to see the data prior to the year and then get the LAST price based on the BEG_DATE, per vendor. I came up with a Measure that shows a 1 for dates before the select date range and a 0 what is in the date range. This is the start but I then need to only show the LAST price. Here's a screenshot of what this currently looks like and I'll include the Measure. I will use the 'beginning of year price' as a column in another table. Any help would be greatly appreciated!
Dates B4 Date Range =
VAR MinDate = CALCULATE( MIN( Dates[Date] ), ALLSELECTED( Dates ) )
VAR MaxDate = MAX( Dates[Date] )
VAR CurrYr = CALCULATETABLE(VALUES(FCI_RAWMAT_PRICE_HIST[PROD_ID]),FILTER(ALLSELECTED(Dates),Dates[Date]>=MinDate && Dates[Date]<=MaxDate))
VAR PrevYr = CALCULATETABLE(VALUES(FCI_RAWMAT_PRICE_HIST[PROD_ID]),FILTER(ALL(Dates),Dates[Date]<MinDate))
VAR Result = EXCEPT(PrevYr,CurrYr)
RETURN
IF(COUNTROWS(Result)>0,COUNTROWS(Result),0)
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
2 Replies
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
