Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
Solved! Go to 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")
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
@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.
show what the error is and is it possible to see an example from the date column
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
107 | |
89 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |