Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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".
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".
Is it DAX?
Then just use HOUR([DateTime]). Time.Hour is PowerQuery function.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |