Thank you Brian. I will check this out. I was able to get my date table relationships setup and it is working now, except my Fiscal QTRs are off. I setup the initial parameters for Fiscal month start date, etc. and it shows the correct setup in the query but the date tables still contain the yearly quarters.
1 Like
@dmartin ,
I think the Cheat Sheet will help clarify this, but in the Extended Date Table when you create fiscal years and quarters, it still also retains the calendar year and quarter for flexibility, unless you specifically hide or remove those fields.
- Brian
Is there a way to change [date] to show Fiscal Quarters. the calendar quarters are of no use to me and if I use one of the Fiscal Year fields within the calendar, it doesn’t give me the ability to filter by month or day. Example: If I use the [date] field as a filter - it lets me filter year, month, qtr, day. But if I use Fiscal Quarter as a filter it only shows the Fiscal Quarter as options. I want the ability to not only filter by Fiscal Quarter but also to filter within the Fiscal Quarter by year, FQTR, month, day.
Is that possible?
Thanks,
Dale
Hi @dmartin,
Are you referring to a date hierarchy?
If so, mark your date table as a Date table, the automatically generated hierarchy will disappear but you can easily create your own by dragging fields from the date table on top of eachother.
So in your case the fiscal year, fiscal quarter, fiscal period and date.
I hope this is helpful.
Hi Melissa, that seems to work however, I don’t see an option for Fiscal Month, I only see Fiscal year, and Fiscal Quarter. I don’t show a Fiscal Month column and that is what I am looking for. For example, I want to be able to select the month of July, which is in my Fiscal third quarter.
Hi @dmartin
I’m going to chime in here. Fiscal month is the same thing as “Month Name” column in the date table.
If you select July for the month. You will get amount for the Fiscal Month of July. Yes, July would be part of your fiscal third quarter amounts.
I hope this helps
1 Like
Thanks Keith. I did figure that out after I sent the comment. Thank you. Ok, one other thing. The month’s are showing up in alphabetical order, what do I sort by to get them to show up in the proper order. Is there a month number column?
Is there a video within the Enterprise DNA training that shows how to setup Year over Year comparisons using the Extended Date Table?
I believe @BrianJ sent you information sorting on data within date table “New Cheat Sheet” message.
I also believe that all videos that is done is using the extend date table.
There are also video within the learning center that set up year over year.
I hope this helps.
Keith
Thanks - I found it in the Cheat Sheet. Sort by MonthofYear. Good to go. Now just to find the year over year video and see if I can get that figured out.
Thanks for your help Keith!
Hi
Apologies if this has already been asked.
Can you set up the extended Date table in a Power BI file and then globally set it so that it automatically is part of any new Power BI report? Or do you need to copy the M code into a query and create the table each time you start a new Power BI report?
1 Like
Hi…I’ve been thinking about the same thing. I was planning on just setting up a Power bi file with the date table ready to go and just have to adjust where necessary. The Power bi file template file will be my starting point for any power bi file and save as a different file name.
I would also keep an eye on the forum to see if there is any updated to the M code for the extended date table.
thoughts?
thanks
keith
1 Like
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