cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
allenshapiro Frequent Visitor
Frequent Visitor

Date Separation - separating dates from timestamps

I am accessing Redshift data via direct query (not loading the data as the update cycles are hourly). The date is in a timestamp format in the originating table. 

How can I separate the date from the date timestamp. All of the DAX commands I use are assuming that the field is text based, but the timestamp format is a value. Any help is greatly desired.

 

The reason for the  questions is I am trying to get the count of id's that were active between two dates. When there are duplicate date times within the dataset it throws an error. I've tried two different approaches but neither works due to the timestamp issue. See the two different approaches below 

1. 

Active Segment = CALCULATE(DISTINCTCOUNT(devices_captured[device_id]),DATESBETWEEN(devices_captured[file_timestamp],TODAY(),TODAY()-7))
 
2.
Last 7 Days = VAR Last_Date =lastdate(daily_stats[date]) return CALCULATE(devices_captured[Segment_Count], FILTER(ALL(daily_stats[date]),daily_stats[date]>Last_Date -7 && daily_stats[date]<= Last_Date))
 
Any help is greatly appreciated
1 REPLY 1
allenshapiro Frequent Visitor
Frequent Visitor

Re: Date Separation - separating dates from timestamps

The second formula was cut off:

Last 7 Days = VAR Last_Date =lastdate(daily_stats[date]) return CALCULATE(devices_captured[Segment_Count], FILTER(ALL(daily_stats[date]),daily_stats[date]>Last_Date -7 && daily_stats[date]<= Last_Date))

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors