Filter in measure on date/time but column has multiple times for same date and doesnt work

[Anonymous]

Hey I have this formula to filter my values on yesterday and if its monday it should display last friday. The problem is that I cant filter on date because the column is a date/time format with multiple times for the same date so the diagram appears empty for some reason. I think the solution to this problem is to either somehow say in my formula take all times in a certain date or to put all times to 00:00:00 in my column
formula now:

YesterdayEdits = CALCULATE(COUNT('Rdates'[S_SEQ]);IF(WEEKDAY(TODAY();1) = 2;'Rdates'[LOG_TIMESTAMP] = TODAY() -3; 'Rdates'[LOG_TIMESTAMP] = TODAY() -1))

yes there can be duplicates usually 2 of the same kind

sample data:
S_EQ       |         LOG_TIMESTAMP     |    DEPARTMENT

1497817            9-12-2019 13:22:42               A
1497817            9-12-2019 14:13:30               A
1567895            9-12-2019 15:16:30               A
1584748            9-12-2019 12:40:12               A

So assuming you read this at 10/12/2019 the result should be a bar diagram of:
A      4

[tex628]

I'd say the easiest way to resolve this is just to make a copy of the [LOG_TIMESTAMP] column and format it to normal date which you then use instead in this calculation. If you don't want to make a copy of the column you can use the YEAR(), MONTH() and DAY() dax syntax to convert the timestamp to a plain date in the middle of the calculation.

Br,
J

[Anonymous]

hi @tex628 

 Yes, but how do I do this formatting? Do you have an example of a formula that does this or can you apply it to my formula I listed below? Making a copy of the column probably isn't that difficult but I'm stuck on the formatting. Not sure if it's easier to do on SQL level or PowerBI level.

[tex628]

Create a new column and reference the timestamp column. 


Then convert the datatype of the column:

[Anonymous]

hi @tex628 

This does not work for some reason my bar diagram remains empty. I did test if it could be the formula. When i remove the timestamp filter data is shown. So it is a problem with the date/time I think even if it is date datatype the time is still there but hidden since formatting removes it. Idk though

Edit: I changed it the way you said so it said for example 11/12/2019 then in my bar chart as axis i put timestamp then it works with value count of shipment_seq BUT when i use my formula even as simple as count seq with a filter that timestamp is today -1 and put it as value then my diagram is empty no clue why.

datetime formatted as 21/11/2019datetimedate

So even after formatting PowerBI still see it as seperate values which explains why it is empty. I think either in  DAX formula or in sql the time part needs to be stripped from the date part so 11/11/2019 can be filtered upon like normal dates are.

[tex628 · Accepted solution]

You are correct, the custom column somehow keeps the datetime format... 

Change the custom column to this instead and it should work.

Timestamp = DATE(YEAR('Table (2)'[LOG_TIMESTAMP]); MONTH('Table (2)'[LOG_TIMESTAMP]);DAY('Table (2)'[LOG_TIMESTAMP]))