Is it possible to code this in DAX?

[georgecrowley]

Hello World!

Is it possible to code this in DAX - im having a hard time doing so and reltively new to DAX!

=(X/Y)^(1/((P-Q)*100))-1

Where X and Y are related in the same column - P and Q are also related in the same column.

However X and Y are not in the same column as P and Q

X is in the same row as P, Y is in the same row as Q 

However X and P are not in the same row as Y and Q

X	P
Y	Q

Finding it hard to relate to a specific cell in DAX

[FreemanZ]

Easily in Excel, but seems not possible in DAX, where the data is evaluated column by column. If you look at the DAX codes, mostly refer to columns or tables. 

[georgecrowley]

X & Y are given net conversion rates whist P & Q are Distrupts

For example

-0.02	0.75%
0	0.50%

[georgecrowley]

I think its worth noting that the table would look like this, so would need more than a 2x2 table and i would like it to update when the data refreshes (if possible)

Disrupt	Header	Header	Header	Header	Header	Header	Header	Header
-0.02	Cell	Cell	0.75%	Cell	Cell	Cell	Cell	Cell
Cell	Cell	Cell	Cell	Cell	Cell	Cell	Cell	Cell
0	Cell	Cell	0.50%	Cell	Cell	Cell	Cell	Cell

I think I need somehting to refer to a specfic cell in a way

[ValtteriN]

Hi,

There is a hidden function called OFFSET which can help you.

E.g.

Data:

Dax:

Measure 25 =

var offsetindex= offset(1,ALLSELECTED('Table (15)'[Index]),orderby('Table (15)'[Index],asc))

var x = SELECTEDVALUE('Table (15)'[Column1])

var y =

CALCULATE(MAX('Table (15)'[Column1]),ALL('Table (15)'),'Table (15)'[Index]=offsetindex)

var p = SELECTEDVALUE('Table (15)'[Column2])

var q =

CALCULATE(MAX('Table (15)'[Column2]),ALL('Table (15)'),'Table (15)'[Index]=offsetindex) //calculate column2 value based on idex offset

return

if(ISBLANK(offsetindex),"NA",

DIVIDE(x,y)^(1/((p-q)*100))-1)


End result:
There are also other ways to do this kind of calculation using idex, but here I am specifically using relation to other "cells" as the basis for calculation.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[NanDeb]

I believe it could be possible... however, how are X&Y and P&Q related in the table? Is it just consecutive records, or dates ? Can you share  sample data