Forum Discussion
How do I return the first value by order (ignoring blanks)?
Hello everyone, I have the following raw data from the pricing sharepoint: ID; Date on which the price was modified (updated), Start/End of the price period, the respective price), and a key (ID...
You need to apply FIRSTNONBLANK to the date. Then use that date as the filter to get the price at that date.
Also look into FIRSTNONBLANKVALUE - that might save you a step.
Or use the pedestrian way
22 Price = var m = min('Table'[Modified]) return CALCULATE(sum('Table'[Price End P2]),'Table'[Modified]=m)Hi Anonymous
Please refer to sample file with the solution https://www.dropbox.com/t/0WkCrxsXlYxewGbb22' Original Price = VAR CurrentIDTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[OriginalID] ) ) VAR NonBlankTable = FILTER ( CurrentIDTable, Data[22' Price] <> BLANK ( ) ) VAR FirstRecord = TOPN ( 1, NonBlankTable, Data[Index], ASC ) RETURN MAXX ( FirstRecord, Data[22' Price] )Thank you so much, @tamerj1
It works perfectly and it is exactly what I needed.
Thank you lbendlin
I created the following formula:
22' Original Price =
CALCULATE(FIRSTNONBLANK('AllRegions'[22'Price],'AllRegions'[22'Price]<>BLANK()),
FILTER(ALLEXCEPT('AllRegions','AllRegions'[Key]),
MAX(AllRegions[Index])))
The problem is that it finds the minimum price for each item, but I need the price that was valid in the first non blank period.
Any ideas what am I doing wrong?
lbendlin
Super User
Super UserYou need to apply FIRSTNONBLANK to the date. Then use that date as the filter to get the price at that date.
Also look into FIRSTNONBLANKVALUE - that might save you a step.
Or use the pedestrian way
22 Price =
var m = min('Table'[Modified])
return CALCULATE(sum('Table'[Price End P2]),'Table'[Modified]=m)