cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ronny_hagen Frequent Visitor
Frequent Visitor

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 New Contributor
New Contributor

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 Regular Visitor
Regular Visitor

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 Visitor
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
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 Regular Visitor
Regular Visitor

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

Re: Possible Tip/Trick: Dynamic Date Dimension Table

Yea got it.Thanks ^^

robcrockford Frequent Visitor
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 Regular Visitor
Regular Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

@robcrockford please see picture below 🙂 

 

Capture.PNG

 

 

faacq Frequent Visitor
Frequent 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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors