cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ALeef Member
Member

Re: Possible Tip/Trick: Dynamic Date Dimension Table

I edited the table above to inclue ISO Week number, which also helped fix the Week Ending values.  Should be more accurate now.  Please let me know if you figure out how to break it!

RJ Regular Visitor
Regular Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

 

 

To get dates to sort in the correct order I used this one

 

MonthT = FORMAT([Date], "MMM") & " " & [Year])

MonthN = MONTH([Date])

 

And then sorted by SUM OF MonthN as a value

 

 

 

dATES.GIF

 

 

 

Edit Actually there is a much better way to do this as shown below

 

http://community.powerbi.com/t5/Desktop/Sorting-by-Month-as-Text-Jan-Feb-Mar-etc/m-p/9242/highlight/...

 

GilesWalker Established Member
Established Member

Re: Possible Tip/Trick: Dynamic Date Dimension Table

I am probably missing something but when I enter the DateTable = CALENDAR (MINX('FactTable', [Created Date]), NOW()) in a new table it says it cant find FactTable. I changed this to a table I had imported and then it says Created Date cannot be found.

 

I am very new to DAX so would appreciate if you could explain a bit further please as this looks very useful.

 

Thanks,

RJ Regular Visitor
Regular Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

 

Did you put for minx 

 

1 The table name   'Account Transactions', 

2  The field name    'Account Transactions'[Date]) ,

 

DateTable = CALENDAR(              minx (     'Account Transactions',      'Account Transactions'[Date]     ) ,         today ())

ALeef Member
Member

Re: Possible Tip/Trick: Dynamic Date Dimension Table

@GilesWalker : [Created Date] refers to a field in my Fact Table, so you will have to change your field name to something that has a date in it.

 

 

RJ Regular Visitor
Regular Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

GTR Member
Member

Re: Possible Tip/Trick: Dynamic Date Dimension Table

Great techniques, looking to tinker with this a bit more to incorporate it for personal use.

Thanks!

lanceengland Frequent Visitor
Frequent Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

Nice tip. As usual, there are several ways to do the same thing with DAX. A function similar to CALENDAR is CALENDARAUTO which scans all the date columns and returns a range of dates from the earliest date to the latest date. It is very handy.

OmarSaka Regular Visitor
Regular Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

@lanceengland However, the CALENDARAUTO isn't dynamic right? I mean it scans for all the dates from earliest to the latest. But if the data source is updated, the CALENDARAUTO doesn't update automatically. Someone can correct me if i'm wrong. 

 

Edit: I just realized that CALENDARAUTO is in fact dynamic. Tried it with an Excel data source. Removed some dates and the table named DateTable (created with calendarauto) updated its date range after a refresh.

lanceengland Frequent Visitor
Frequent Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

Yes. Dynamic is this sense is at process time i.e. refreshing the data model from the data source. On that note, while I haven't verified it, I'm guessing CALENDARAUTO would not work in Direct Query mode. That said, if you were in Direct Query mode, you would handle the dynamic date range at the data source.

 

 

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