Quotient of value and previous date value dax equation issue

[JanGunni]

I need some help adjusting the following equation. Currently it returns the max RO A01-Report'[Stage 1 SF]. I want it the exact value of the previous date. 

The idea with max was it would filter all days before the current and give me the Stage 1 SF value for the max date (which would be n-1 days).

CIP SF S1 =

VAR CurrentDate = 'RO A01-Report'[date]

VAR CurrentValue = 'RO A01-Report'[Stage 1 SF]

VAR PreviousValue =

CALCULATE(

MAX('RO A01-Report'[Stage 1 SF]),

FILTER(

ALL('RO A01-Report'),

'RO A01-Report'[date] < CurrentDate && NOT(ISBLANK('RO A01-Report'[Stage 1 SF]))

)

)

RETURN

IF(

NOT(ISBLANK(PreviousValue)),

DIVIDE(CurrentValue, PreviousValue),

BLANK()

)

[v-yangliu-msft · Accepted solution]

Hi  @JanGunni ,

You can try the following dax.

CIP SF S1 =
var _value=
MAXX(
    FILTER(ALL('RO A01-Report'),
    'RO A01-Report'[date]<MAX('RO A01-Report'[date])&&'RO A01-Report'[Stage 1 SF]<>BLANK()),[Stage 1 SF])
return
IF(
    _value<>BLANK(),
    DIVIDE(MAX('RO A01-Report'[Stage 1 SF]),_value),BLANK())

If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.