Automated Date Table generation
It would be nice to have a tool for automatically generating a date table - there are lots of sample SQL code and M code out there for doing so, but it would still be nice to have a built-in facility for this.
Good suggestion Eric, thanks! If others think it’d be helpful, please add your votes!
Andrew Humphrey commented
PowerPivot has this feature already. Can't understand why PBI doesn't have it as well. Hopefully it would be relatively easy to add since the two products seem to be very closely related.
it would also be nice to have the ability to set timezone and current date for running WTD, MTD, QTD, YTD Calculations
I think this a very good suggestion and can be done quickly as well. Currently we create the DimDate in SQL server and then import the same here. Similar tool is also available in Excel as an Add-on.
In PowerBI tool should be able to do the following:
1. Select the Date Range or Auto Generate based on some Date field
2. Allow the user to define Financial Year,
3. Add Granular Columns such are Day of Week, Day of the year, Month Name, etc... ( some functions already there in Edit Query > Date & Time
4. Define Start of the Week & Define Weekends
5. Attach a Holiday Calendar
6. Add Time Buckets
7. Add AM / PM or 24 Hour Periods
Once all these functions are developed the user can click on Generate the table & the system can detect relations in a popup menu and select/de-select and save.
It would be nice to have this automatically generated date table to expose a default hierarchy so we can use it immediately
Hila Galapo commented
My main problem with the current date hierarchy that it can't be modified. I'm in Australia where the Financial Year starts July 1st. Also, sometimes you'd like to label your Date data
Yes, love this idea, still feels awkward to create a continguous dates table for the time intelligence features to work
Chimene B commented
A lot of my customers want a Fiscal Year function available as well as a GetDate() functionality. E.g. Fiscal year, FY to Date, Current date to FY, Past FY. In addition, day and month filters e.g. next/past 30/90/180 days.
It would be better if we didn't need a date table and could group dates by months, quarters and years like we do in pivot tables
Tristan Cook commented
Its great that you can already generate a list of numbers but doing the same for dates , including Month Names, Day Names and week numbers would cut down a lot of the code I have to write or reduce the need for a seperate time Index table
Michael Grimm commented
I agree, this is a very repetitive task that should just be configurable.