Forum Discussion
Grouping clients using a DATEDIFF & TODAY( ) function
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:
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")
4 Replies
- Ahmedx
Super User
show what the error is and is it possible to see an example from the date column
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 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/FalseI 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")
