DateTime.FixedLocalNow - DateTime Function

DateTime.FixedLocalNow is a Power Query M function that returns a datetime value set to the current date and time on the system, which is fixed and will not change with successive calls.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

DateTime.FixedLocalNow() as datetime

Description

DateTime.FixedLocalNow returns a fixed datetime value set to the current date and time on the system. Unlike DateTime.LocalNow, which may yield different values across successive calls, DateTime.FixedLocalNow provides a constant value during the execution of an expression.

Examples

Let’s delve into an example to illustrate how DateTime.FixedLocalNow works. Below is a simple example demonstrating the output of this function when the query was executed:

// At the time of running this query the function returns
// #datetimezone( 2024, 7, 23, 12, 56, 24.0738740)

DateTime.FixedLocalNow()

Consistent Timestamps Across Multiple Calls

Now, let’s explore how DateTime.FixedLocalNow maintains consistency across multiple calls within the same query. The following code illustrates this behavior:

/* The result of this query is two identical values. Output:
  [ After5Secs     #datetime( 2024, 13, 6, 16, 22, 20 ),
    CurrentValue = #datetime( 2024, 13, 6, 16, 22, 20 ) ] */
let
  GetFixedDate = ()=> DateTime.FixedLocalNow(),
  InvokeAfter5Secs = Function.InvokeAfter(GetFixedDate, #duration( 0,0,0,5) ),
  Output = 
    [ After5Secs   = InvokeAfter5Secs, 
      CurrentValue = DateTime.FixedLocalNow() ]
in
  Output

In this code:

GetFixedDate: This function returns the current fixed datetime using DateTime.FixedLocalNow.
InvokeAfter5Secs: This uses Function.InvokeAfter to call GetFixedDate after a 5-second delay.
Output: This record stores the results, showing that both After5Secs and CurrentValue have the same datetime value.

Since both values use DateTime.FixedLocalNow, they remain identical even when one is delayed by 5 seconds. This shows the fixed nature of DateTime.FixedLocalNow.

Comparing with DateTime.LocalNow

To understand the difference, let’s compare the example using DateTime.LocalNow in a similar context:

/* The result of this query is two distinct values. Output:
  [ After5Secs     #datetime( 2024, 13, 6, 16, 22, 25 ),
    CurrentValue = #datetime( 2024, 13, 6, 16, 22, 20 ) ] */

let
  GetDate = ()=> DateTime.LocalNow(),
  InvokeAfter5Secs = Function.InvokeAfter( GetDate , #duration( 0,0,0,5 ) ),
  Output = 
    [ After5Secs   = InvokeAfter5Secs, 
      CurrentValue = DateTime.FixedLocalNow() ]
in
  Output

In this scenario:

GetDate: This function returns the current datetime value using DateTime.LocalNow.
InvokeAfter5Secs: This uses Function.InvokeAfter to call GetDate after a 5-second delay.
Output: This record stores the results, showing that After5Secs and CurrentValue have different datetime values.

When using DateTime.LocalNow, the function fetches a new datetime value each time it’s called. This results in different timestamps, as seen in the output, because DateTime.LocalNow reflects the exact time at the moment of each call.

Example with Reversed Order

Finally, consider the following example where the order of the function calls is reversed:

/* The result of this query is two distinct values. Output:
  [ After5Secs     #datetime( 2024, 13, 6, 16, 22, 20 ),
    CurrentValue = #datetime( 2024, 13, 6, 16, 22, 25 ) ] */

let
  GetFixedDate = ()=> DateTime.FixedLocalNow(),
  InvokeAfter5Secs = Function.InvokeAfter( GetFixedDate , #duration( 0,0,0,5 ) ),
  Output = 
    [ After5Secs   = InvokeAfter5Secs, 
      CurrentValue = DateTime.LocalNow() ]
in
  Output

Here:

GetFixedDate: This function returns a fixed datetime value using DateTime.FixedLocalNow.
InvokeAfter5Secs: This uses Function.InvokeAfter to call GetFixedDate after a 5-second delay
Output: This record stores the results, showing After5Secs and CurrentValue with different datetime values.

The field that uses DateTime.FixedLocalNow (After5Secs) returns a datetime value that is earlier than the one using DateTime.LocalNow (CurrentValue). This happens even though the fixed value was invoked 5 seconds after the current one. This demonstrates that DateTime.FixedLocalNow captures the datetime value at the start of the query and keeps it unchanged throughout the execution of the expression.

Learn more about DateTime.FixedLocalNow in the following articles:

Timing M Query Execution In Power Query And Power BI (Revisited)
This article shows how you can retrieve the execution time of a query in Power Query. » Read more

Other functions related to DateTime.FixedLocalNow are:

DateTime.LocalNow

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/datetime-fixedlocalnow