cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Calculated Column based on two columns in table

@thomasbassett Somewhere you are comparing Text and Number types. Make sure you are comparing Text with Text and Number with Number datatypes. Also, could you please post the expression that you are using (after changing as per your requirement). 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





thomasbassett Frequent Visitor
Frequent Visitor

Re: Calculated Column based on two columns in table

@PattemManohar I've created a new tran date column that is just a copy of the original tran date but changed the format to text. I'm still getting the same error. When the error shows, it appears to be highlighting the final part of the formula (see screenshot). I've also placed the expression with my changes below.

 

trandatediff error.PNG

 

TransDateDiff =
VAR _CurrDate = 'Pre Gateway'[Tran Date (for TransDateDiff)]
VAR _PrevDate = CALCULATE(MIN('Pre Gateway'[Tran Date (for TransDateDiff)]),FILTER(ALL('Pre Gateway'),'Pre Gateway'[Email]=EARLIER('Pre Gateway'[Email]) && 'Pre Gateway'[Tran ID] = EARLIER('Pre Gateway'[Tran ID])-1))
RETURN DATEDIFF(IF(_PrevDate=BLANK(),_CurrDate,_PrevDate),_CurrDate,DAY)

Super User
Super User

Re: Calculated Column based on two columns in table

@thomasbassett Could you please check and confirm that "Tran ID" column is of number datatype. If not, please change that to number data type and try. Leave other fields datatypes as is (Dates as date and email as text)



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





thomasbassett Frequent Visitor
Frequent Visitor

Re: Calculated Column based on two columns in table

@PattemManohar Ah okay we might have an issue here. In the sample data I presented I showed "Tran ID" as a number, but this is in fact was a manual field that I had to create as it didn't exist in the data. It is a combination of a customers email address and the transaction date. Therefore, I cannot make the type Number.

 

Is there a way to create a unique number based on a text field? If so, I could then use that...