Forum Discussion
Grouping clients using a DATEDIFF & TODAY( ) function
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")
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