cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shivam
Helper III
Helper III

Can anybody help me implement this logic in DAX? We are using direct query.

x, y, z are three different fields/columns with values x1, x2...; y1, y2...; and so on.

 

in case of x we just know "x1" and we need to find x2, x3, x4... in following manner:
x2= x1+y1-z1
x3= x2+y2-z2
and so on

 

all y and z field values are given but in case of x we just know x1.

How can we apply this in Dax to find x2, x3,....?

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @shivam 

i assume that you have a unique date or index column. Otherwise you nee to create one. 

X =
VAR X1 = 1 --inset the initial value 
VAR CurrentIndex = TablName[Index]
VAR TableBefore =
    FILTER ( TablName, TablName[Index] < CurrentIndex )
VAR Y =
    SUMX ( TableBefore, TablName[Y] )
VAR Z =
    SUMX ( TableBefore, TablName[Z] )
RETURN
    X1 + Y - Z



View solution in original post

5 REPLIES 5
shivam
Helper III
Helper III

x, y, z are three different fields/columns with values x1, x2...; y1, y2...; and so on.

 

in case of x we just know "x1" and we need to find x2, x3, x4... in following manner:
x2= x1+y1-z1
x3= x2+y2-z2
and so on

 

all y and z field values are given but in case of x we just know x1.

How can we apply this in Dax to find x2, x3,....?

 

 

@tamerj1 

tamerj1
Super User
Super User

Hi @shivam 

i assume that you have a unique date or index column. Otherwise you nee to create one. 

X =
VAR X1 = 1 --inset the initial value 
VAR CurrentIndex = TablName[Index]
VAR TableBefore =
    FILTER ( TablName, TablName[Index] < CurrentIndex )
VAR Y =
    SUMX ( TableBefore, TablName[Y] )
VAR Z =
    SUMX ( TableBefore, TablName[Z] )
RETURN
    X1 + Y - Z



How would these work if you can explain me? 

SUMX ( TableBefore, TablName[Y] )
SUMX ( TableBefore, TablName[Z] )

 

Hi @shivam 
Simple mathmatics

x2= x1+y1-z1
x3= x2+y2-z2 = x1+y1-z1+y2-z2 = x1 + (y1+y2) - (z1+z2) = x1 + SUMX (Y) - SUMX (Z)

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors