cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ronny_hagen Advocate I
Advocate I

Re: Possible Tip/Trick: Dynamic Date Dimension Table

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 Resident Rockstar
Resident Rockstar

Re: Possible Tip/Trick: Dynamic Date Dimension Table

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() )
mbdtz Advocate III
Advocate III

Re: Possible Tip/Trick: Dynamic Date Dimension Table

This is still relevant and so awesome

 

I'm using this to join a set of data that only has discreet dates which don't allow use of the previousyear() functions etc

 

thanks for sharing 😄

 

ps. I also added an extra column:
FiscalYear = IF(MONTH(DateTable[Date]) > 6, DateTable[Year] &"/"&right(DateTable[Year],2)+1, DateTable[Year]-1 &"/"&right(DateTable[Year],2)) 

tvermeer
Regular Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

I know this post is abit old but thanks so much!  Creating this date table has completely changed how I can link some of my sales and budgeting data based on dates.  Cheers!

weilip1803
Frequent Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

I am having a problem, The formula above that I had used is sumerizing the data. How can I avoid the summary. The ordering of the column in charts are sorting the month names by alphabetical order how can i make it go by chronological order?

mbdtz Advocate III
Advocate III

Re: Possible Tip/Trick: Dynamic Date Dimension Table

@weilip1803

 

It may not be elegant but I have added a 'MonthSort' column to my table

 

MonthSort = DateTable[Year]&IF(DateTable[MonthofYear]<10,0,"")&DateTable[MonthofYear]

 

Then simply select the column you're using on the chart in the data tab, click 'modeling' and select the 'Sort by Column' button

 

Let me know how that goes 🙂

 

 

weilip1803
Frequent Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

Yea got it.Thanks ^^

robcrockford
Frequent Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

Hi, new to PBI, when you say:

 

"The table is built using the following formula:

DateTable = CALENDAR (MINX('FactTable', [Created Date]), NOW())"

 

Where should this formula be added? Assume you create the column headers for the rest and add formulas into row 1?

 

Thanks in advance

 

mbdtz Advocate III
Advocate III

Re: Possible Tip/Trick: Dynamic Date Dimension Table

@robcrockford please see picture below 🙂 

 

Capture.PNG

 

 

faacq
Regular Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

I have generate a dynamic date table but I'm not able to create a relationship with the main table where my data resides. In both tables I have the NameofMonth and the Year.

When I try to manually create the relationship I get this error.  What am I doing wrong?

1.PNG

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors