Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all!
Where it displays FALSE, I am trying to obtain the last non blank value from the "Previous Quarter - Average Invoice Unit Price".
I can do this this when the quarter is -1 but if there is more than 1 quarter with no value it doesnt work.
Average Invoice Unit Price =
CALCULATE(
AVERAGE('MaterialPurchasing'[Invc.price]),
FILTER('MaterialPurchasing',
'MaterialPurchasing'[Invc.price]<>0))
Previous Quarter Average Invoice Unit Price =
CALCULATE(
[Average Invoice Unit Price],
DATEADD ('Date_Table'[Date],
-1, QUARTER))
Price Every Quarter =
VAR _Current = [Average Invoice Unit Price]
VAR _Previous = [Previous Quarter Average Invoice Unit Price]
RETURN
IF(_Current <> BLANK() && _Previous = BLANK(), _Current,
IF(_Current <> BLANK() && _Previous <> BLANK(), _Current,
IF(_Current = BLANK() && _Previous <> BLANK(), _Previous,
"FALSE")))
You will need a column in your date dimension with sortable year & quarter values, like 20194,20201, 20202 etc.
But then this could work:
Previous Quarter Average Invoice Unit Price =
VAR curqtr = MIN('Date_Table'[YearQuarter])
VAR prevqtrs = CALCULATETABLE(VALUES('Date_Table'[YearQuarter]),ALL('Date'), 'Date_Table'[YearQuarter]<curqtr)
RETURN IF(NOT(ISBLANK([Average Invoice Unit Price])),LASTNONBLANKVALUE(prevqtrs,[Average Invoice Unit Price]))
Hi @Jenn3301,
You could apply the logic from the example below to solve your problem. However in your case the code is supposed to be more complicated - should you provide a mock dataset, I can try to adapt this logic for your measures.
In plain text for convenience:
NewValue =
IF ( NOT ISBLANK ( [Value] ),
[Value],
VAR CurrentPeriod = [Period]
VAR LastValuePeriod = MAXX ( FILTER ( Data, NOT ISBLANK ( [Value] ) && [Period] < CurrentPeriod ), [Period] )
RETURN MINX ( FILTER ( Data, [Period] = LastValuePeriod ), [Value] )
)
Best Regards,
Alexander