Within this category you can also find a topic on creating a dynamic Start- and Enddate.
.
In addition there will also be a video on that topic in the Time Intelligence series Brian and I are doing. We’ll add a link here as soon as it’s released to the Enterprise DNA Channel.
Hi Melissa,
The optional parameters work fine, would it be possible to add a selection parameter to show Monday as Weekday 1 please, so users can select either 0 or 1.
Paul
That sounds like a nice addition indeed. So added the optional WDStartNum parameter this allows you to set the [DayOfWeek] numbering from either 0-6 or 1-7. However if omitted, the standard numbering will be 0-6.
Keep in mind that this doesn’t affect the start of the week day, that will always remain Monday as is custom to this Calendar and the ISO week logic.
that’s very cool. Thank you for that.
I’d like to ask you a favour, what if the StartDate and EndDate I wanted to grab from the Table “Sales”, column “Sales Date”, using MIN and MAX?
Thanks for this video — it is very good—in fact, excellent. Unfortunately, I have discovered an issue with the “Fiscal Year” Calculation.
As I am not working now, so I am using the “Adventure Works” MS Access database to build my models. As this database begins in 2001, this causes a problem when calculating the Fiscal Year. The Extended Date Table returns FY02 and FY2 as when the +1 is added if less than the FYStartMonth, it is returning the 2 without the 0 in front. If I click on FY02 and FY2 together, I get the correct results. Obviously, this only happens thru 2009 — from 2010 forward, everything works great.
I am not good at M, but I believe I did find the problem but not sure how to resolve but I will keep working on it. An M expert should be able to resolve quickly. I could solve it in excel :>) — or I will remap the data to post-2010 dates.
How many users need FY back in 2001-2009? Not many but…
Again, thanks for your contribution and you have been a great addition to Sam’s team!
Thanks
Wally
Must admit I new that could be an issue but forgot to fix it It’s sorted now and I also updated the FY Offset. With some community development this is turning into a great Date table
.
Watch out for the eDNA Challenges, it’s a great way to practice with different types of data, learn and pick up new techniques others used. I hope to see some of your work there.
Your update worked great ---- very fast. The “Text.PadEnd” code worked great. I will not forget that one!
I hope to join the eDNA Challenge, but while I can build some great reports, the techniques I have seen you and others use are outstanding and I need to up my game in that area. I will get there!
If you have the time don’t wait to join the challenges and just jump in. There’s so much to learn from them whether you’re a beginner or a seasoned analyst… you’ll find this is a great community giving lots of feedback, tips, tricks and all sorts of support.
Here’s a link to the challenge, released yesterday. I hope you’ll reconsider and I look forward to seeing your entry
Funny you should write that as I was just looking for the latest challenge. I will use it to focus my training. Do not expect much from me at the beginning, but…
Dear Forum Members,
At first I would like to thank @Melissa for the excellent M script.
I’ve immediately given it a try. It helped me very much.
The data created by that script refreshes without a problem in PB Desktop.
But the troubles have come when I’ve published my report with date table into PB Service.
The refresh there gave me an error:
[Unable to combine data] Section1/Holidays/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
That has to do something with the table of holidays which I read from the Internet and use as an option in the M script.
Could you please give me a piece of advice what to do with this error message.
Hello @Melissa,
Trying to prepare data for you I’ve discovered that the problem is located in my holidays table.
The holidays have to be listed for all the years from sales table.
I’ve prepared the file using your sales data from Ultimate Beginners Guide to DAX course (it’s just an example). Example.pbix (412.7 KB)
After taking another look at your error the answer is that you cannot combine an external data source with another query. See this article for more details and how to resolve it.
Hello @Melissa,
I’ve read the article you suggested and got the idea. Thanks.
But I still have a questions.
I watched Enterprise DNA videos on date table, on setting start and end dates and on creating a holiday table. Could we set up holiday table years/dates range dynamically and then use it as optional table in the function that creates date table? (Your colleague Brian got the holiday dates from the Internet but finally created Holiday table in Excel. Does that mean that holiday table used in date table function ought to be “static”?)
Regards @AdamJZ
Yes but you’d have to avoid that combination of external data source with another query.
.
Not exactly, for example I created a huge Holiday table in excel and stored that on SharePoint. Now I dynamically get Holidays from that SharePoint file without having to rely on an internet site remaining active, keeping the same data structure and so on. It also avoids that external data source issue.
There are lots of sites on the web with this information, but one that I particularly like is this one which has all countries’ holidays listed through 2030. It is structured very similarly to the site I used in my video, so the same web scraping techniques should work there too: