Question: In the calculated column below, we are determining the Last Year Unit price for suppliers. However, new suppliers lack data, resulting in blank rows. I need to amend the logic in the column to populate these blank values with those from existing suppliers for the same material number. Our data contains pairs of suppliers, and we want to use the value from the supplier that has the data.
I have managed to create a ‘test’ secondary calculated column that provides the results (shown below). I need to figure out a way to do this all in one column.
Any help or suggestions would be greatly appreciated!
Main Calculated Column:
LYUnitPriceUSD_DEC =
VAR __LastYearKey = Table1[Column1] & "_" & Table1[Year]-1 & "_Q" & Table1[Quarter]
VAR __ExcelValue = LOOKUPVALUE(
Table2[PriceLastYear],
Table2[Key],
__LastYearKey)
VAR __SAPValue =
LOOKUPVALUE(
Table3[AveragePrice],
Table3[Key],
Table1[Column1] & "_" & Table1[Year]-1)
VAR __CurrentRateYearCode =
LEFT( RELATED(Table4[CurrencyYear]) , 3 )
& "_" &
IF(
Table1[Year] > YEAR( TODAY() ),
YEAR( TODAY() ),
Table1[Year])
VAR __RateMultiplication =
LOOKUPVALUE(Table5[RateMultiplier],
Table5[CurrencyYearKey],
__CurrentRateYearCode)
VAR __SAPValueCurrentRate = __SAPValue * __RateMultiplication
VAR __Result = COALESCE(__ExcelValue,__SAPValueCurrentRate)
RETURN __Result
Test Column for Reference:
Find Duplicate Value test =
VAR __Exclude = CALCULATETABLE( VALUES(Table6[SupplierName]), Table6[Exclude] = "Exclude" )
VAR __AlternateLYValue =
IF( NOT( ISBLANK(Table1[LYUnitPriceBudatUSD_DEC]) && NOT( Table1[SupplierName] IN __Exclude ) ),
Table1[LYUnitPriceBudatUSD_DEC],
CALCULATE(
MAX( Table1[LYUnitPriceBudatUSD_DEC] ),
FILTER( Table1,
Table1[Year] = EARLIER( Table1[Year] )
&& Table1[ArticleNo] = EARLIER( Table1[ArticleNo] )
&& NOT( ISBLANK( Table1[LYUnitPriceBudatUSD_DEC]))
&& NOT( Table1[SupplierName] IN __Exclude ) ) ) )
RETURN __AlternateLYValue
Disclosure: This question was assisted by AI.
