How can I get the values from a measure for previous date to do math on the next date?

[Jphillips]

While I understand measures don't actually exist like columns, my need here is to get the value of a measure from the date/row before current, and get the absolute delta between the two. This is for a moving range of a z score.

Above is my current measure DAX. I attempted to create a virtual table. [Z] is a measure using a couple of other measures and columns.

[Z] is calculated as:

IF([r3_den_total] < 1,BLANK(),
DIVIDE( ([r3%] - [Average R3%] ),SQRT(([Average R3%] * (1-[Average R3%]))/[r3_den_total]) )
)

As you can see in the table visual, [Prev Day Z] is returning blank.

My desired output would look like:

Date	R3%	Z	Prev Day Z
2021-07-20	60.71%	1.034
2021-07-21	55.56%	0.782	1.034
2021-07-22	51.18%	0.053	0.782

I've been hung up on this for a couple of weeks now, and any help or guidance would be immmeasurable!

[Vera_33 · Accepted solution]

Hi @Jphillips 

It is difficult to write DAX measure without enough context, just based on your sample and your not working [Prev Day Z], your dates are consecutive, right? You may try this, please modify the Table name and Column name accordingly, AND pls provide some sample data in a format which people can copy in the future

test = 
VAR PreDate=SELECTEDVALUE('yourTable'[Date])-1
VAR T1=ADDCOLUMNS(
       ALLSELECTED('yourTable'[Date]),"z1",[Z])
RETURN
MAXX(FILTER(T1,[Date]=PreDate),[z1])

[Jphillips]

@Vera_33  This worked beautifully. I'd actually come up with the below yesterday, but it doesnt seem to calculate correctly, but your query is much cleaner and easy to read. Thank you so much!

Perf R3 MR = 
VAR __prevR3 =
    CALCULATE ('Performance Measures'[r3%], PREVIOUSDAY('Calendar'[Date]) 
    ) 
var __prevR3den =
    CALCULATE('Performance Measures'[r30_den_total], PREVIOUSDAY('Calendar'[Date])
    ) 
var __avgR3 = 
    CALCULATE( Performance[Average R3%], ALLSELECTED(Performance)
    )

RETURN ABS(  IF( __prevR3den < 1 ,BLANK(),
    DIVIDE( (__prevR3 - __avgR3 ),SQRT((__avgR3 * (1- __avgR3 ))/ __prevR3den) )
    ) - [R3 Z] )