Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
38 | |
21 | |
20 | |
13 |
User | Count |
---|---|
125 | |
41 | |
38 | |
26 | |
24 |