Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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])
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])
@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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |