Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
valQuentine
Frequent Visitor

Can we change the value of StartOfDay to a different HOUR instead of 12:00:00 of each day?

I have this scenario:

 

Instead of each StartOfDay to be 12:00:00, the TradingDay begins at 5:00:00 AM of each day. So, my solution is to make a custom column that will determine the correct TradingDate for each transaction based on the given condition (i.e. 5:00:00 AM start of Trading Day). I got it to work in Excel thru HOUR() function but I can't get it to work in powerBI. My Excel formula is shown below.

 

=IF(HOUR(DateTime)>4,TEXT(DATE(YEAR(DateTime),MONTH(DateTime),DAY(DateTime)+1),"mm/dd/yyyy"),TEXT(DATE(YEAR(DateTime),MONTH(DateTime),DAY(DateTime)),"mm/dd/yyyy"))

 

=time.hour([DateTime]) is not working in my case. I don't know what's wrong. I have tried this:

 

TradDate = IF(Time.Hour([DateTime])>4,[DateTime]+1,[DateTime])

 

And it returned an error "Failed to resolve name Time.Hour. It is not a valid table, variable, or function name."

 

Can you help me with this please? 

 

Thanks a lot.

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee

@valQuentine

You may need a calculated column as below 

TradDate = IF(HOUR('Table'[datetime])>4,DATEVALUE('Table'[datetime])+1,DATEVALUE('Table'[datetime]))

And then format it to "mm/dd/yyyy".

 

Capture.PNG

View solution in original post

3 REPLIES 3
Eric_Zhang
Employee
Employee

@valQuentine

You may need a calculated column as below 

TradDate = IF(HOUR('Table'[datetime])>4,DATEVALUE('Table'[datetime])+1,DATEVALUE('Table'[datetime]))

And then format it to "mm/dd/yyyy".

 

Capture.PNG

Thank you very much @Eric_Zhang

Chihiro
Solution Sage
Solution Sage

Is it DAX?

Then just use HOUR([DateTime]). Time.Hour is PowerQuery function.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.