DAX Help

[Anonymous]

Hi there.

See sample of a dataset I am working on below.

M1Reg	M1End	NumericalChangePretoPost
1	3	2
4	3	-1
2		-2

I am trying to calculate the change between M1Reg column and M1End column. However some of the M1End responses I have are blank and I don't want the DAX to calculate the change like it has done on the 3rd row. 

Currently using

NumericalChangePreToPost = M1[ M1End]-M1[ M1Reg] 

What can I insert to the DAX to ensure that it doesnt return an answer if the M1End column is blank? 

[Anonymous]

With the if statement it should return negative value of first column

[Anonymous]

I don't want it to return a negative value of the first column, I want it not to calculate anything if either the first or second column is blank. It is currently assuming a blank cell = 0 which isnt the case.

What can I insert to ensure it doesn't calculate on a blank cell?

[Anonymous · Accepted solution]

NumericalChangePreToPost =if(not(isblank(M1[ M1End])),

M1[ M1End]-M1[ M1Reg])

[Anonymous]

Hi there.

This works if M1End is blank but can you include within the DAX if either of the two columns are not to calculate - i.e if M1Reg or M1End is blank, do not calculate? Thank you so much for this.

[Anonymous]

Hello

NumericalChangePreToPost =if(isblank(M1[ M1End]),-M1[ M1Reg],

M1[ M1End]-M1[ M1Reg])

[Anonymous]

Hi there.

Thank you for your response..

It is still giving me back a calcuation even if the cell was blank though?

[y3wan]

Couldn't you creat a new column of;

newcolumn = IF(M1END=0, Blank(), M1[M1END]-M1[M1REG])

For blank values in either 1 column

newcolumn = IF(M1REG=0, Blank(), IF(M1END=0, Blank(), M1[M1END]-M1[M1REG]))

fyi I meant Blank ( ) bucket in the formulas above