DV Calculated column and IF statement

[ckserra33]

Hi everyone.  I am new to Dataverse and I am using a calculated column to show the remaining value left on an item.  This is working correctly, and the code I am using is below:

cr3db_totalcost - (cr3db_monthlydepriciationamount * cr3db_monthsfromacquire)

The issue is the numbers will eventually go negative.  I need it to stop at 0.  I was trying to use an IF statement, but cannot figure out the correct syntax. 

Something like If(NBV column >0,cr3db_totalcost - (cr3db_monthlydepriciationamount * cr3db_monthsfromacquire),0)

But I am 100% positive I am not approaching it correctly.

Thank you for any help!

Chris 

[joaodsantos · Accepted solution]

Hi,

I feel like this is possible, but you might need to do two calculated fields instead of one. Something like

Calculated Field 1: Total Depreciation = cr3db_monthlydepriciationamount * cr3db_monthsfromacquire

No condition for this one

Then:

Calculated Field 2: Current Value

If (Total Amount < Total Depreciation)

Current Value = 0

Else

Current Value = Total Amount - Total Depreciation

Hope this helps

[dpoggemann]

Hi @ckserra33 ,

Note (IN PREVIEW) but Power Fx in Dataverse should be able to easily handle your situation here with the advanced formulas that can be used... Example article explaining the functionality:  https://powerapps.microsoft.com/en-us/blog/power-fx-formula-columns-in-dataverse/ 

The logic you are looking to do here would be basic with Power Fx (in preview) in Dataverse... 

[ckserra33]

@joaodsantos Thank you very much.  I figured it was something like that, thanks again!

[ckserra33]

@pdp Thank you very much for the idea, I just felt more comfortable doing it with 2 columns at this point in my knowledge of DV.  Thank you very much!