Help with grouping modified date into today, yesterday etc.

[Valley_Girl]

Sorry if I've already posted this but can't find my original post!

I'm trying to group my datetime column "modify time" so it contains data like this:

New column name: Last Modified

Today: 4

Yesterday: 7

2 days ago: 3

3 days ago: 4

I have the datetime column but I also have "Modified Date" which is a date column to work with.

I'm struggling with the statement, I assume I want something like this:

IF [Modified Date] =  TODAY THEN "Today" ELSE IF [Modified Date] > TODAY -1 AND < TODAY THEN "Yesterday" etc.

Any help would be greatly appreciated!

[amitchandak]

@Valley_Girl , In you date table create a column like this

Date Type = SWITCH(TRUE(),'Date'[Date]=TODAY(),"Today"
,'Date'[Date]=TODAY()-1,"Yesterday"
,'Date'[Date]=TODAY()-2,"2 Days Ago"
,'Date'[Date]&"")

Sort it on Date

Join it date of date table with date of your table

[Valley_Girl]

@amitchandak  Thank you, I think this would only work if I was to continue with 3,4,5,6 days etc. but I'd like to expand the range the older the data is so for example last week, last month which I don't think can be done with a switch? I'd need to use an IF statement to achieve this- Is that correct?

[V-lianl-msft]

Hi @Valley_Girl ,

Try to create the column.

Column = var diff = DATEDIFF('Table (2)'[Modified Date],TODAY(),DAY)
return SWITCH(TRUE(),
            diff<7,diff&" days ago",
            7<=diff&&diff<30,TRUNC(diff/7)&" weeks ago",
            diff>=30,TRUNC(diff/30)&" months ago")

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Valley_Girl · Accepted solution]

Not sure if it's the most elegant solution but I went with this in the end:

Last Modified =

IF('sysaid'[Modified Date]=TODAY(),"0 Days",

IF('sysaid'[Modified Date]= TODAY()-1,"1 Day Ago",

IF('sysaid'[Modified Date]= TODAY()-2, "2 Days Ago",

IF('sysaid'[Modified Date]= TODAY()-3, "3 Days Ago",

IF('sysaid'[Modified Date]= TODAY()-4, "4 Days Ago",

IF('sysaid'[Modified Date]= TODAY()-5, "5 Days Ago",

IF('sysaid'[Modified Date]= TODAY()-6, "6 Days Ago",

IF( 'sysaid'[Modified Date] <= TODAY()-7, "Over A Week") )))))))