Custom Column else if divide

[Anonymous]

Current column is [Days to Close] new custom column is Avg. Days Worked 

I would the like formula to say if [days to close] is greater than or equal to 181 then 90 and if [days to close] is less than or equal to 180 then divide the days to close by 2. 

the first part of my formula works but the second part isn't and I have spent too many hours trying to rework the formula. 

=if [Days to Close]>=181 then 90 else if [Days to Close]<=180 then Value.Divide ([Days to Close], 2)

[lit2018pbi]

@Anonymous 

create a new column  as 

Column 2 = IF('Table'[Days] >= 181, 90, IF('Table'[Days] <= 180, 'Table'[Days]/2))

[harshnathani]

Hi @Anonymous ,

In M Code

Custom Column=if [Days to Close]>=181 then 90 else if [Days to Close]<=180 then [Days to Close]/ 2

In DAX (Calculated Column)

Column =
SWITCH(
TRUE(),
Table[Days to Close] >= 181 , 90,
DIVIDE(Table[Days to CLose],2)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

@harshnathani Thank you, I am using M code. My formula changed and this is what I have entered. I am still getting the "Token Else expected" error. 

custom column=if[Days to Close]>501 then [Days to Close]/4 else if [Days to Close]<=500 then [Days to Close]/3 else if [Days to Close]<180 then [Days to Close]/2

Do you think this would be easier in DAX? 

[harshnathani]

Hi @Anonymous .

Use this for M Code.

if [Days to Close]<180 then [Days to Close]/2 else if  [Days to Close]<=500 then [Days to Close]/3 else [Days to Close]/4

Additionally, you can use add contional columns

To nest an if within another if we use the same if – then – else methodology, as shown below

= if [thing to to test #1]  = "something else" then [do this if true]
else if [thing to to test #2]  = "something else" then [do this if true] else 
[do this if false]

https://exceloffthegrid.com/power-query-if-statements-for-conditional-logic/

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[lit2018pbi · Accepted solution]

@Anonymous 

Create a new column using the following DAX

custom column = IF('Table'[Days] > 501, [Days]/4,IF( [Days]<=500 && 'Table'[Days] >= 180, [Days]/3, IF([Days]<180, [Days]/2)))

[Anonymous]

let's consider your column name is days_close.

You can try this DAX in your calculated columns using Divide function,

=if([days_close]>=181, 90, if([days_close]<=180, Divide([days_close],2),0))