Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
BW40
Helper I
Helper I

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:

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.
1 ACCEPTED SOLUTION
BW40
Helper I
Helper I

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_3-1679949557290.png

 

View solution in original post

4 REPLIES 4
BW40
Helper I
Helper I

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_3-1679949557290.png

 

BW40
Helper I
Helper I

BW40_0-1679921334340.png

@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.

BW40_1-1679921425770.png

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_2-1679921906863.png

BW40
Helper I
Helper I

@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.

BW40_0-1679667379155.png

BW40_1-1679667404188.png

 

 

 

 

Ahmedx
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.