Hi @KieftyKids as @Keith suggested, setting up a Power BI template with the Date Table in there would be the best way to go, set your theme too if poss.
2 Likes
@DavieJoe …great idea 
never thought of it, but it likely would have come to me once I get more into power bi
1 Like
this is exactly how I use the Date table, instead of as a function that I enable to run, I have parameters for my start and end dates, which feed the table logic. that makes adjustment to a new dataset easy.
I have also gone through and pre-loaded that Date Table into my blank PBI that I use to build all subsequent reports in (just saving under a new name each time), and then I’ve gone through and addressed all of the sort-by changes, hidden the columns that don’t need to be in the final version (like the sort by columns and current period columns), and finally grouped the data in the model view so that Month columns are all toge3ther, and Year columns, etc.
This way, when I start a new PBIX file, I have a strong date table that needs very little fussing with to make it a truly elegant solution.
1 Like
Hi Melissa - What should I modify to ensure that the Week Start day is Sunday? Week Ending would be on Saturday. We do not use a special FY…just a regular calendar year here in the US.
Hi @richmont,
Your fiscal year would start in January which is month 1. I would recommend that you still do your FY start month. Actually every company will have FY start even if its January which would be your calendar year.
I found this in the message within this same posting that might help.
thanks
Keith
Thanks Keith. I think my question is maybe a bit broader. Melissa states in another post that this is essentially an ISO based calendar. I need a non-ISO based calendar for our company. All of the dates in here are centered around an ISO calendar, but I am looking for a version of this that is Not an ISO…just a regular calendar year here in the USA. We do not use the ISO standard. I would love to keep the ISO info there and just comment it out, but I am struggling how to essentially change the specific code lines to not be based on ISO.
1 Like
Hi @richmont,
I’m sure I can help you with that, could you create a new topic and provide some additional details on how the week numbers should be handled at the calendar year start and end?
Please link to this post OR @ mention me, thanks.
1 Like
HI @richmont ,
Sorry for the confusion.
@Melissa,
I would also like to know this answer too, just in case i run up against it in the future.
thanks
Keith
@Melissa Thank you Melissa and for all you do! Here in the US, many companies use a standard Gregorian calendar for their “fiscal year”, including my company. Jan 1 to Dec 31.
It would be great to have a version of your date table based on this. We would define Sunday as the start of the week (not the first workday necessarily, that would still be Monday). But a week would be defined as Sunday thru Saturday. I’m sure many readers here in the US would find a Gregorian based date table useful.
Two other related things:
- You have a weekending clause already. Could you possibly add a weekstarting one (to both versions)?
- In the ISO code version, what does this line mean? Specifically, the last part in parenthesis.
StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 2,1) ), 3)),
Hi @richmont,
Thanks for your reply, I am away now but expect to get back to you later today, tomorrow at the latest.
It’s actually a piece of redundant code. Formatted it makes more sence, I think.
From the inside out; I’m getting the weekday name of a specific date, keep its first 3 letters and have that start with a capital.
@Melissa Many thanks! One question. I am getting odd behavior with the Year & Month, Quarter & Month, and Week & Month. I have two models, one for sales, and one for quality issues. The quality issues model includes a direct query dataset (from the sales model), and it is in Mixed mode. When I use those fields on a chart visual, I get a NaN error. I do not get this error on the fully import mode model (the sales model). But I do get it on the mixed mode model (the quality dataset). It only seems to apply to those fields. I can bring in quarter, week, etc with no errors. Any idea what is causing this and how to fix?
Hi @Melissa was concerned about the application of the formula, provided by @sam.mckay in his article about
Showcasing Workday Number in Power BI Using DAX, to your “Date Table”?
When applying it accoding to the fields of your “Date Table” it does not seem to work and I am not able to understand what is wrong.
Thanks in advance!
Hi @Jose,
Welcome to the Forum!
I’d be happy to look into this for you, can you please create a new Topic, supply your work in progress file with a mock up of the desired outcome.
See this great reference on how to do that.
https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763
Sure! thanks How can i create a new topic, thouhght I had already created one by posting the message?
1 Like
Hey Jose,
hope all is good with you
You’ve created an additional message/post on an existing thread
If you go to this link Latest topics - Enterprise DNA Forum and select the New Topic button to create a new topic.
