topic Re: How to set to 0 negative values in a formula in App Development

How to set to 0 negative values in a formula

Manuel174102 — Fri, 23 Sep 2022 14:01:51 GMT

Hello everyone,

I have a kpi that is showing the average lead time.

The formula is the following:

Avg({<[Posting Date]-={2015, 2016}>}[Processing Time] )

The thing is, some of this lead times ( aka processing time) are negative, and I want them to be 0. How can I modify this formula to make that work?

thanks in advance

BrunPierre — Fri, 23 Sep 2022 14:58:18 GMT

Perhaps this

Avg({<[Posting Date]-={2015, 2016},[Processing Time]={"> 0"}>}[Processing Time])

sidhiq91 — Fri, 23 Sep 2022 15:00:34 GMT

@Manuel174102 You can add the below line in the Script Editor first and then you can continue to use your expression above.

if([Processing Time]<0,0,[Processing Time]) as [Processing Time].

This should resolve the issue.