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
TimAdams
Frequent Visitor

Creating Timestamp based on FixedNow()

I have created a "Last Data Refreshed" table using the query below:

 

let
   LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),
   ConvertedtoTable = #table(1, {{LastRefresh}}),

In

 ConvertedtoTable

 

How can I create a timestamp column based on LastRefresh?

1 ACCEPTED SOLUTION

I have got it working using your logic as follows:

 

let
   LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),
   EpochDate= #datetime(1970,1,1,0,0,0),
   TimeStampWorking=LastRefresh-EpochDate,
   ConvertedtoTable = #table(3, {{LastRefresh,EpochDate,TimeStampWorking}}),
   #"Renamed Columns" = Table.RenameColumns(ConvertedtoTable,{{"Column1", "Refresh Date"}, {"Column3", "TimeStamp Working"}}),
    #"Inserted Total Seconds" = Table.AddColumn(#"Renamed Columns", "Total Seconds", each Duration.TotalSeconds([TimeStamp Working]), type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Total Seconds",{{"Total Seconds", "Timestamp"}})
in
    #"Renamed Columns1"

 

Thanks for your help

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

If I understand what you are going for, you could turn it into a function like this:

 

let fnLastRefresh = () =>

let
   LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),
   ConvertedtoTable = #table(1, {{LastRefresh}})
in
 ConvertedtoTable

in 
    fnLastRefresh

Then just create a custom column based on this function and expand the table.

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Its more, how do I create the timestamp as it is not part of the date column types

How about you provide an example of the output that you are looking for? Because I apparently don't get what you are talking about, my converted time has a date and time like "1/26/2019 9:55:18 AM" which seems like a timestamp to me but apparently not?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Sorry I am talking about a SQL Timestamp, which should look something like this - 400989863

 

I did see alot of threads about comnverting a timestamp to a datetime. but not vice versa.

Back in the day I created a measure to convert Unix time to UTC time. I looked like this:

 

UTCTime = 
VAR UnixDays = MAX([UnixTime])/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)

So, to convert it back from UTC Time to Unix time, you would do this:

 

Measure = 
VAR __utc = [UTCTime]
RETURN (__utc - DATEVALUE("1/1/1970"))*60*60*24

So, you would need to know your "epoch" date. For Unix it is 1/1/1970 and it varies depending on which SQL Server flavor you are using. Which version of SQL are you using? MySQL, Oracle, SQL Server, Postgres?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I have got it working using your logic as follows:

 

let
   LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),
   EpochDate= #datetime(1970,1,1,0,0,0),
   TimeStampWorking=LastRefresh-EpochDate,
   ConvertedtoTable = #table(3, {{LastRefresh,EpochDate,TimeStampWorking}}),
   #"Renamed Columns" = Table.RenameColumns(ConvertedtoTable,{{"Column1", "Refresh Date"}, {"Column3", "TimeStamp Working"}}),
    #"Inserted Total Seconds" = Table.AddColumn(#"Renamed Columns", "Total Seconds", each Duration.TotalSeconds([TimeStamp Working]), type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Total Seconds",{{"Total Seconds", "Timestamp"}})
in
    #"Renamed Columns1"

 

Thanks for your help

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.

Top Solution Authors