Eric Hutton on 06 Jan 2015 01:27:45
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.
Administrator on 23 Jan 2015 06:25:21
Good suggestion Eric, thanks! If others think it'd be helpful, please add your votes!
- Comments (10)
RE: Automated Date Table generation
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.
RE: Automated Date Table generation
it would also be nice to have the ability to set timezone and current date for running WTD, MTD, QTD, YTD Calculations
RE: Automated Date Table generation
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.
RE: Automated Date Table generation
It would be nice to have this automatically generated date table to expose a default hierarchy so we can use it immediately
RE: Automated Date Table generation
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
RE: Automated Date Table generation
Yes, love this idea, still feels awkward to create a continguous dates table for the time intelligence features to work
RE: Automated Date Table generation
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.
RE: Automated Date Table generation
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
RE: Automated Date Table generation
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
RE: Automated Date Table generation
I agree, this is a very repetitive task that should just be configurable.