Complete date table in ACCESS

Anything related to PowerPivot and DAX Formuale
ce77
Posts: 8
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: 999
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: 8
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: 8
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: 999
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: 8
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!

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

Re: Complete date table in ACCESS

Postby ce77 » Mon Sep 24, 2018 8:41 pm

Ciao Matt,
I completed the table and now shall start looking for issues, once I find any I'll let you know. Thanks.

Meanwhile, you can observe an issue due to a date table incomplete at the end of the last year included in it. It is enough that you check Rob Collie's book (Power Pivot and Power BI), page 230. He calculates the following

[Total Sales DATEADD 1 Year Back] =
CALCULATE (
[Total Sales],
DATEADD ( Calendar[Date], -1, YEAR )
)

using a table wich has an incomplete 2004 year. The result is a wrong calculation of 2003 Sales in the 2004 row. Try yourself if you like. It is enough you take your calendar table, cancel the last 6 month of 2004, refresh and calculate the above. Put it in a pivot with calendar year on rows and you will find an inccorect nr in the last year (2004), when calculating the sales of 2003 with dateadd

Thanks again for your support

Francesco

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

Re: Complete date table in ACCESS

Postby ce77 » Thu Oct 25, 2018 9:43 pm

Ciao Matt,
I finally provide you proof of this issue, sorry I did not doo it in the below post directly, I was in a hurry.

I could not find issues yet if the date table starts in the middle of a year (so I still keep that thing in the model, sooner or later I shall find it),
but here the proof of issues when the date table ends in the middle of the year (solved with a date table that completes the year up to dec 31, which is the set I use now - still, again, the date table starts in the middle of one year though: 1st july 2001 to 31st dec 2004)

Running the model in Rob Collie's book (Power Pivot and Power BI), page 230 you get this. In red the wrong value

Year Total Sales Total Sales 1 Year Back
2001 $3.266.374
2002 $6.530.344 $3.266.374
2003 $9.791.060 $6.530.344
2004 $9.770.900 $4.771.584
Grand Total $29.358.677 $14.568.301

Here what you see if you complete Rob's table putting all days in 2004, up to dec 31st (in green the value now correct)

Year Total Sales Total Sales DATEADD 1 Year Back
2001 $3.266.374
2002 $6.530.344 $3.266.374
2003 $9.791.060 $6.530.344
2004 $9.770.900 $9.791.060
Grand Total $29.358.677 $19.587.777

I would be honored to be the first one showing you that Marco Russo is right, you can have issues! Is this so (am I the first one)?

Best regards and thank for the immense halp you provide to BI young specialist like me /willing to learn: I am almost done with the definitve guide - God it is tough!)

Francesco

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

Re: Complete date table in ACCESS

Postby MattAllington » Fri Oct 26, 2018 5:53 pm

Thanks for sharing. Yes I would also expect the behaviour you describe in the scenario. If you don’t have all the days on 2004, I would not expect it to fetch all the days in 2003. But it you are currently half way through 204 (day it is 1 July 2004, if I were comparing 2004 with 2003 then I wouldn’t want to see all days in 2003, just the days from 1 Jan to 30 June 2003. My assumption is this will work.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

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

Re: Complete date table in ACCESS

Postby ce77 » Sun Oct 28, 2018 11:08 pm

Hi Matt,
thanks for the reply. I understand your point and agree.

Anyway, suppose a user is looking at the data to judge it and it is 2010. Following Rob's suggestion to have the maximum date in the calendar table equal to the last day of business
is not a good choice as the sales in 2004, dated 1 year back, would only be partial and you would not notice that at first glance. So maybe Marco says what he says to avoid that a
not expert user might draw wrong conclusions.
Last note: at this point I've assumed another issue with Rob's set appears calculating Sales one year ahead, in fact this is what I find (in red the bad value)

Year Total Sales Total Sales 1 Year Ahead
2001 $3.266.374 $2.724.633
2002 $6.530.344 $9.791.060
2003 $9.791.060 $9.770.900
2004 $9.770.900
Grand Total $29.358.677 $22.286.593

Best regards


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests

cron