Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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")

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.