Grouping clients using a DATEDIFF & TODAY( ) function

[BW40]

I am trying to categorize clients by their sale date, which should be simple but I am not sure how to transate the command into Power BI syntax. I have three categories: client sales date < 180 days ago, client sales date >= 180 days ago, and and clients with no sale date at all.

It won't let me use a DATEDIFF function in a custom column. If I could put it in a column, I could then group by an IF formula to make conditional groups. The custom column equation DATEDIFF('table'[Sales date], TODAY( ), day) gives me an error

The logic is something like this:

IF DATEDIFF([sales date], TODAY( ), day) >= 180, "over 180 days"

     ELSEIF DATEDIFF(sales date, TODAY(), day) < 180, "under 180 days"

ELSE "no sales date"

DATEDIFF(sales date, TODAY(), day) works as a measure but not a column and it won't let me make conditional groups from a measure.

[BW40 · Accepted solution]

I figured it out. Power BI it required me to break it into two different items (including one that controlled for blanks or I lost customer IDs).

First, I had to create the datediff function: Days since Sale= DATEDIFF('table'[sale date], TODAY( ), day)

Then I had to put that measure into a column to break the values into groups. Once it was a column, I can use it as a column or filter by its groups:

Days since sale column = SWITCH( TRUE( ), 'table' [days since sale], > 180, "over 180 days",
     'table'[days since sale] <= 180 && 'table'[days since sale] > 0, "closed within the last 180 days",
     'table'[days since sale] = blank ( ), "hasn't closed yet")

[BW40]

@Ahmedx When I used the same DATEDIFF formula for a custom column that worked as a formula, it gives me the error message above even though it was copied and pasted from the formula. The forumla worked as a value and can be added to my table but it will not allow me to use it in a conditional category formula.

Above is the example of the dates in my table and the result of the datediff formula. I want to break it into three categoes, sales date >= 180 days, < 180 days, or is blank then be able to filter by those categories. Since the number of days changes daily, I want it to be dynamic rather than manually sorted into groups so I don't have to manually fix the groups each time.

When I put the datediff value into an IF function it says it cannot the value of Text type to True/False

[BW40]

@Ahmedx  I copied and pasted the same equation that is working as a formula into the custom column, after I remove the table name from the equation, the custom column box says "no syntax errors detected." However, when I push OK an error message pops up saying DATEDIFF wasn't recognized even though it was copy and pasted from the working formula.

[Ahmedx]

show what the error is and is it possible to see an example from the date column