topic Re: Last non blank quarter in DAX Commands and Tips

Last non blank quarter

Jenn3301 — Wed, 20 Sep 2023 05:52:13 GMT

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

Re: Last non blank quarter

barritown — Thu, 21 Sep 2023 08:37:18 GMT

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

Re: Last non blank quarter

sjoerdvn — Thu, 21 Sep 2023 14:35:19 GMT

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