Complete date table in ACCESS

Anything related to PowerPivot and DAX Formuale
ce77
Posts: 4
Joined: Fri Sep 14, 2018 10:31 pm

Complete date table in ACCESS

Postby ce77 » Fri Sep 14, 2018 10:34 pm

HI Matt,
I've been studying your books along with Rob Collie's and Ferrari and Russo's.

In order to have the least possible issues usaing CALCULATE in conjunction with DATEADD, it would be nice to have a complete date table for Years 2001-2004 (from january 1st 2001 to dic 31 2004) in access format. Do you have it at hand and can procide it?

Thanks

MattAllington
Posts: 985
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Complete date table in ACCESS

Postby MattAllington » Mon Sep 17, 2018 8:21 am

I understand why you are asking. Marco Russo keeps telling me that the calendar table should go to the end of the year. I don't have that, but I have an Excel version in the download that you can extend yourself if you like. FYI, I have written hundreds of formulas using the current incomplete calendar table and never had an issue.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

ce77
Posts: 4
Joined: Fri Sep 14, 2018 10:31 pm

Re: Complete date table in ACCESS

Postby ce77 » Tue Sep 18, 2018 11:28 pm

Thanks Matt, I shall download it

On the subject, I had the same idea as you until a few days ago, when I found an issue. Point is that the issue seems to show only if the incomplete table part is
towards the end of the date interval. I mean: I had the issue with an incomplete table (from Rob Collie, you can also find the problem in his very book tables printed) that was starting from july 2001 and ended on july 2004, so it was incomplete at the beginning and at the end. Using yours from Learn DAX, the issue disappeared as you have one that has a complete 2004 (so the table goes from july 2001 up to dicember 31 2004, the incompleteness is only at the beginning of the interval).

My point is, now, try to understand what can happen when the beginning of the dates is not january 1st. So, now I shall download your excel table, complete 2001, include it in the model and try to create a problem with the table I have from your DAX book (starting in july) and see if it resolves using the complete table.

Sometimes I have noticed that issues are pretty difficult to spot, especially with dates. At the same time, Marco is so advanced that I have little doubts that his suggestion is not related to issues you/myself might find and maybe not see. I am currently finishing my journey with the Deifnitive Guide to DAX, a very complex book. I started with Collie, then you and then Russo/Ferrari.

Should I find problems having the beginning of the dats not being january 1st I shall let you know (note: in Europe FY starts on january 1st, maybe you have less issues or none even if I might have one since in US Fiscal Years start on april)

Thanks
Francesco

ce77
Posts: 4
Joined: Fri Sep 14, 2018 10:31 pm

Re: Complete date table in ACCESS

Postby ce77 » Tue Sep 18, 2018 11:32 pm

Hey Matt,
can't find the Excel table, where is it? Sorry I am not able to find it

Thanks
Francesco

MattAllington wrote:I understand why you are asking. Marco Russo keeps telling me that the calendar table should go to the end of the year. I don't have that, but I have an Excel version in the download that you can extend yourself if you like. FYI, I have written hundreds of formulas using the current incomplete calendar table and never had an issue.

MattAllington
Posts: 985
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Complete date table in ACCESS

Postby MattAllington » Wed Sep 19, 2018 7:27 am

If you don't have it, please download the sample files from my website again https://exceleratorbi.com.au/supercharg ... /#download There is a file called 7 tables in that zip file
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

ce77
Posts: 4
Joined: Fri Sep 14, 2018 10:31 pm

Re: Complete date table in ACCESS

Postby ce77 » Wed Sep 19, 2018 9:26 pm

Got it

I had it of course, did not get you were referring to that when writing "download"

Thanks!


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests