Forum Discussion

allenshapiro's avatar
allenshapiro
Regular Visitor
7 years ago

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
Need Help

1 Reply

  • allenshapiro's avatar
    allenshapiro
    Regular Visitor
    7 years ago

    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))