Last non blank quarter

[Jenn3301]

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")))

[sjoerdvn]

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]))

[barritown]

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

My YouTube vlog in English

My YouTube vlog in Russian