Adding/Subtracting Hours from a date from Redshift Source using DAX

[tomq04]

I'm currently trying to subtract 8 hours from a date/time stamp hosted in Redshift.  This function works just fine when conntected to a SQL Server source (using literally the same table) but it is only subtracting 29 seconds from the Redshift table.

I'm currently using a last ditch effort  DAX formula (Adjusted_Posted = list_financial_events[posted_date] -(8/24)), but we have tried several iteration in both Power Query and DAX to perform this function, with the result always being -29 seconds.

Thanks,

Tom

[v-yuezhe-msft]

Hi @tomq04，

Could you please post sample data of your table? In Query Editor of Power BI Desktop, you can add a custom column using the following formula. You can view the example in the screenshot below.

[DateColumn]-#duration(0,8,0,0)

Thanks,
Lydia Zhang

[tomq04]

That did work, but it knocks us out of direct query mode.  I'll see if that is acceptable for our use case.

[tomq04]

I mispoke earlier, I had been connected to our SQL server data warehouse.  Attached is what is happening when connected to our Redshift Server.  redshift error

[v-yuezhe-msft]

Hi @tomq04,

Do you use import or DirectQuery mode to connect to Redshift?

Thanks,
Lydia Zhang

[tomq04]

We are using direct query, the table we are working with is >15mm records.

[v-yuezhe-msft · Accepted solution]

Hi @tomq04,

Could you please use import mode to upload some records of data from Redshift to Power BI Desktop, then check if the issue persists?

Thanks,
Lydia Zhang

[tomq04]

Hi @v-yuezhe-msft

Even after importing the data we are still having the same issue where only 29 seconds is being subtracted from the date.

Screenshot

[v-yuezhe-msft]

Hi @tomq04,

Could you please share me the PBIX file which you use import mode to connect to Redshift?

Thanks,
Lydia Zhang

[tomq04]

I sent you a private message with the onedrive link.  Let me know if that does not work.

Thanks

[v-yuezhe-msft]

Hi @tomq04,

I am not able to reproduce your issue in your PBIX file, please check the following screenshot. Do you use the latest version of Power BI Desktop?



Thanks,
Lydia Zhang

[tomq04]

Hi Lydia,

You are correct this functionality works when imported.  After talking with a technician we determined that we needed to create an idea ticket for the functionality to work with Direct Query with a Redshift data source.