Forum Discussion

ALeef's avatar
ALeef
Resolver II
10 years ago

Possible Tip/Trick: Dynamic Date Dimension Table

I'm going to share the dynamic date dimension table I built, hopefully it is useful to someone!  I named it DateTable for this tutorial, and my fact table FactTable.   The table is built using the ...
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
10 years ago

Hi,

 

I notice you refer to TODAY() and NOW() functions.

 

I am looking to include rolling periods in my date dimension, but I can't seem to get the TODAY() function to work in Power BI Desktop.

 

Using 'Add column' to test a generic 'Current Date' column - to use i.e. to calculate deal age or similar, I get this result.

 

Formula;

= Table.AddColumn(#"Changed Type6", "TESTTODAY", each TODAY())

 

Error Message;

"Expression error: The name 'TODAY' wasn't recognized.  Make sure it's spelled correctly."

 

 

Would you happen to have any useful insights, how to apply this formula in Power BI?

 

 

Thanks.

greggyb's avatar
greggyb
Resident Rockstar
10 years ago

TODAY() and NOW() are DAX functions and are available in the data model. You'd define a new column with a ribbon button called 'New Column'.

 

You are referring to the 'Add Custom Column' button in Power Query. Power Query uses the M data mashup language, which is based on the F# language. In M the way to get the current date-time is as follows:

 

// Power Query
// Current date-time

 = DateTime.LocalNow()

// Current date (without any time portion)

 = DateTime.Date( DateTime.LocalNow() )