Dynamic Date Table (Update Date Table)

I am using date table provided by Sam and is working as expected.

Requirement:
A Date table which start date is 01/01/ Minimum year from some table (say Sales Table) and end date is 12/31/ Maximum year from some table (Sales Table). When data in the table (Sales Table) changes, data in the Date table should be changed.

I tried example from some blogs, but those ideas are not easy as Sam’s Date table. What is the easiest way to make Sam’s Date table dynamic?

Thank You,
Abinash

@abinash

Truly, I don’t know the answer to your question. “What is the easiest way to make the table dynamic?”

Let’s say you have a model and you used Sam’s table to get it started then when you update with the latest data you notice a gap in the dates.

It is not a big deal, here is what you can do. Note the new min/max dates you require, give yourself some padding as needed. Now go to query editor, open the dates table and apply the solution below for your min/max dates.

As shown go to the first line (source) and manually change the dates as per your latest data update and the Dates table will then be fully compliant to the updated fact table.

This was a pretty quick fix for me and allows to move on, it is a good habit to do a minimum of dynamic maintenance and housekeeping. You can just go ahead, edit the formula bar and your are done.

By the way keeping backup files of your updates and work in progress goes without saying.

Hope this helps, although it is not the solution you expected, it is pretty handy and it is good to go.

BR

Richard D.

1 Like

Thank you for your response Richard, I appreciate your time on detailing. I think this is perfect solution when you manually changes the date in Dates table.

But my concern is how can we make it fully dynamics?
Once I create date table, it works for any date changes.

@abinash,

Thank you for your prompt response and I was just thinking about that. Here is what it is, one is responsible to verify data integrity prior to issuing/refreshing a report. If something is wrong it can be fixed but someone has to make an effort and do that first pass.

To keep your eye on the ball you can prepare a checklist of things to verify to make sure everything is under control, this sanity check is a normal part of the process.

Give yourself some float or padding on the date range. I still think you need to systematically check that all your ducks are in a row before producing a report. Such an analysis does not take too much time once you figure it out.

By the way, I am new on the forum too, I am not too sure how that works but if this solves your problem, I appreciate if you “mark” the problem as solved.

Thanks again for your support and we will see what happens. Have a great day.

Richard D.

1 Like

@abinash,

You can make the date table entirely dynamic using the CALENDARAUTO function. Here’s an article that describes how to do it:

Here’s the DAX code from a recent date table I created this way:

DateTable = 
ADDCOLUMNS (
    CALENDARAUTO ( 9 ),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "MonthNumber", FORMAT ( [Date], "MM" ),
    "YearMonthNumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date] ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "FiscalYear", IF (
        MONTH ([Date] ) > 9 , YEAR ( [Date] ) + 1, YEAR ( [Date] )
        ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"
        & FORMAT ( [Date], "Q" )
)

Hope this is helpful.

  • Brian
3 Likes

@BrianJ

Wow, this is very slick solution. I still like going to the query editor, now I am going to find some other reason…

The first article sets the date range based on your fact table. MIN based on (Year[order date] and MAX could be based on YEAR[Paid Date]. That should cover the whole spectrum you want to be looking at.

In your example, I was wondering where the date range was coming from and found out more about CALENDARAUTO in this interesting article.

Note: The problem is, you don’t have a fiscal year option with CALENDAR, so to have the best of both worlds you could use CALENDARAUTO with VARIABLE/filters as per the first article

CALENDARAUTO will remains current even if you are using an outdated fact table.
CALENDARAUTO with filters remains within the boundaries of your fact table.

That was very interesting, thank you for your input and have a great day.

Richard D.
PS: BrianJ gets my vote for solving this issue. Cheers!!!

1 Like

@richard_d,

Thanks. That being said, I find I use Sam’s query to create my date table more often than not. I like the structure and comprehensiveness of the columns in his table. It wouldn’t be hard to recreate all those columns in DAX, but i just haven’t taken the time to do it. (If anyone on the forum has done that already and would be willing to post your DAX, that would be great).

  • Brian
1 Like

Thank you Brian.

Instead of using CALANDARAUTO() I use CALANDAR() function to specify the table I am using.

Dates = 
ADDCOLUMNS (
CALENDAR (DATE(YEAR(MIN(Sales[Date])),1,1), DATE(YEAR(MAX(Sales[Date])),12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthName", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date],2 ),
"DayOfWeek", FORMAT ( [Date], "dddd"),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
)
1 Like