Portland Power Platform User Group

Please login or click SIGN UP FOR FREE to create your PowerPlatformUG account to join this user group.
 View Only
  • 1.  DimDate tables

    Microsoft Power Platform Team
    Posted Apr 26, 2018 12:13 PM
    Hello All, 
    I am a new member to the group and joined the first meetup last night.  This is my first post to the forum, so if there is a moderator out there let me know if I've got this post in the right location. 

    I found all the talks very helpful, and after Brian's talk on Timeline intelligence. I started to wonder if there was a better DimDate table out there that I could be using.

    I have one that I modified from a PowerQuery class over a year ago.  

    What are the best sources for DimDate tables and which tables are people using?  

    I work in manufacturing so I can have unique factory calendars with things like night shift that goes to 2am that I want to include in the data for the previous day. I also have all the business calendar needs like fiscal years.

    Hope this thread can get help others with optimizing their DimDate tables and keeping up on the best standards.

    Thanks,

    Tate




    ------------------------------
    Tate Breswick
    Manufacturing Engineer
    Portland OR
    ------------------------------


  • 2.  RE: DimDate tables
    Best Answer

    Posted Apr 26, 2018 05:08 PM
    ​Hi, Tate and welcome!

    There are several good links that I can recommend when it comes to Date table or Calendar table.

    This is the freshest one from Matt Allington where he talks about building a reusable calendar table with Power Query:
    Build a Reusable Calendar Table with Power Query
    Exceleratorbi remove preview
    Build a Reusable Calendar Table with Power Query
    I wrote a blog on this topic a few years ago over at PowerPivotPro.com. I have learnt a lot about Power Query since that time, plus Power Query has come along in leaps and bounds, and it now has a lot more capability than it had previously.
    View this on Exceleratorbi >

    The "Italians", as always, provide quality material:
    Reference Date Table in DAX and Power BI - SQLBI
    Sqlbi remove preview
    Reference Date Table in DAX and Power BI - SQLBI
    The Auto Date/Time feature available in Power BI presents several limitations: It has a fixed set of rows. It does not handle fiscal years. It does not include weeks. It cannot be shared across different tables in the same data model. Usually, it is necessary to disable the feature and to create a custom Date table.
    View this on Sqlbi >

    I also like Rob Collie's links - if you simply go to his website below and type Calendar table into the search box, you will come up with all sorts of useful posts, including how to create a 445 calendar with Power Query, custom calendars and blending "time of day" analysis with calendar/date analysis.
    PowerPivotPro - Leading Consultants on Power BI and the Microsoft Data Platform
    PowerPivotPro remove preview
    PowerPivotPro - Leading Consultants on Power BI and the Microsoft Data Platform
    1 Self Service Business Intelligence Consulting Firm. Founded by Best Selling Author and Former Microsoft Engineer Rob Collie. WE Transform your business with Power Pivot and Power BI.
    View this on PowerPivotPro >


    This will get you started.
    Good luck!




    ------------------------------
    Kirill Perian
    www.data-driven-solutions.com
    ------------------------------



  • 3.  RE: DimDate tables

    Posted Apr 26, 2018 05:42 PM

    Thanks Kirill for this – I've been wanting a one-stop-shop reference for where to go for Date tables so this is great!

     

    Rachel Dyer  / VP Analytics

     

    CSG Pro } Delighting clients ... since 1993.

    9755 SW Barnes Road, Suite 660. Portland, OR 97225/ 503-445-6303 (direct) 

    503-302-2904 (mobile)/ www.csgpro.com 

     






  • 4.  RE: DimDate tables

    Top Contributor
    Posted Apr 26, 2018 05:15 PM
    Thanks for your post Tate!

    I hope others provide their preferences, but below is a link to a YouTube video where I learned about making a DimDate table with PowerQuery as well as Brian Grant's PowerQuery code for his preferred DimDate table. Let me know what you think.

    P.S. Yes you are posting in the right place :)

    Power BI Create Date Table in seconds using Power Query function
    YouTube remove preview
    Power BI Create Date Table in seconds using Power Query function
    Power BI, and most business intelligence models require a date table. This video will show you how to create one within seconds using a function, allowing you time to go ahead and create your visualisations. Use the link below to get the code. Any problems with the link give me a shout.
    View this on YouTube >


    ----------start------------

    let

        StartingDateAsText="1/1/2014",

        EndingDateAsText="12/21/2020",

        FinancialYearStartingMonth=1,

     

        // Generate base table

    //Old Version: Source = List.Dates(Date.From(StartingDate),YearsToAppend*365,#duration(1, 0, 0, 0)),

    //

        Source = List.Dates(Date.From(StartingDateAsText),Number.From(Date.From(EndingDateAsText) - Date.From(StartingDateAsText) ) +1,#duration(1, 0, 0, 0)),

     

        Transformed=List.Transform(Source, each Date.ToRecord(_)),

        Tabled=Table.FromList(Transformed,Record.FieldValues,{"Year","Month","Day"}),

     

        //Add Full Date Column

        FullDateAddedTable=Table.AddColumn(Tabled,"FullDateAlternateKey",each Date.FromText(Text.From([Year])&"-"&Text.From([Month])&"-"&Text.From([Day]))),

        DateKeyAdded=Table.AddColumn(FullDateAddedTable,"DateKey",each ([Year]*10000)+([Month]*100)+[Day]),

        FullDateNameAdded=Table.AddColumn(DateKeyAdded,"DateFullName",each DateTime.ToText(DateTime.From([FullDateAlternateKey]),"dd MMMM yyyy")),

     

        // Fiscal Year

        FiscalYearAdded=Table.AddColumn(FullDateNameAdded,

            "Fiscal Year",

             each 

                  if Date.Month([FullDateAlternateKey])>=FinancialYearStartingMonth then 

                        Date.Year([FullDateAlternateKey])+1 

                  else 

                        Date.Year([FullDateAlternateKey])

             ),

     

        // Fiscal Month

        FiscalQuarterAdded=Table.AddColumn(FiscalYearAdded,

            "Fiscal Quarter",

             each 

                  if Date.Month([FullDateAlternateKey])>=FinancialYearStartingMonth then 

                       Number.IntegerDivide((Date.Month([FullDateAlternateKey])-FinancialYearStartingMonth),3)+1

                  else

                       Number.IntegerDivide((12+Date.Month([FullDateAlternateKey])-FinancialYearStartingMonth),3)+1

              ),

     

        // Calendar Quarter

        CalendarQuarterAdded=Table.AddColumn(FiscalQuarterAdded, "Calendar Quarter",

              each Number.IntegerDivide(Date.Month([FullDateAlternateKey])-1,3)+1

              ),

     

        // Is Week Day

        WeekDayAdded=Table.AddColumn(CalendarQuarterAdded, "IsWeekDay",

              each

                  if 

                       Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))=Day.Sunday

                       or 

                       Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))=Day.Saturday

                 then 0 else 1 ),

     

        // Day Of Week

        DayOfWeek=Table.AddColumn(WeekDayAdded,"DayOfWeek",each Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))),

        // Day of Week Name

        DayOfWeekName = Table.AddColumn(DayOfWeek,"Day of Week Name",each DateTime.ToText(DateTime.From([FullDateAlternateKey]),"dddd")),

        DayOfWeekNameShort = Table.AddColumn(DayOfWeekName, "Day of Week Name Short", each Date.ToText( [FullDateAlternateKey], "ddd" )),

     

        // Month Name

        MonthName=Table.AddColumn(DayOfWeekNameShort,"Month Name",each DateTime.ToText(DateTime.From([FullDateAlternateKey]),"MMMM")),

        MonthNameShort = Table.AddColumn(MonthName, "Month Name Short", each Date.ToText( [FullDateAlternateKey], "MMM" )),

     

        SystemDate = Table.AddColumn(MonthNameShort, "System Date", each Date.From( DateTime.FixedLocalNow() )),

        RelativeYearIndex = Table.AddColumn(SystemDate, "Relative Year Index", each [Year] - Date.Year( [System Date] )),

        RelativeYearLabel = Table.AddColumn(RelativeYearIndex, "Relative Year Label", each if 

        [Relative Year Index] < -1

        then Text.From( -1 * [Relative Year Index] ) & " Years Back"

        else if

            [Relative Year Index] > 1

            then Text.From( 1 * [Relative Year Index] )  & " Years Forward"

            else if

                [Relative Year Index] = -1

                then "Last Year"

                else if

                    [Relative Year Index] = 0

                    then "Current Year"

                    else if

                        [Relative Year Index] = 1

                        then "Next Year"

                        else "Error"),

        RelativeMonthIndex = Table.AddColumn(RelativeYearLabel, "Relative Month Index", each [Relative Year Index] * 12

     + ( [Month] - Date.Month([System Date]) )),

        RelativeMonthLabel = Table.AddColumn(RelativeMonthIndex, "Relative Month Label", each if 

        [Relative Month Index] < -1

        then Text.From( -1 * [Relative Month Index] ) & " Months Back"

        else if

            [Relative Month Index] > 1

            then Text.From( 1 * [Relative Month Index] )  & " Months Forward"

            else if

                [Relative Month Index] = -1

                then "Prev Month"

                else if

                    [Relative Month Index] = 0

                    then "Current Month"

                    else if

                        [Relative Month Index] = 1

                        then "Next Month"

                        else "Error"),

        RelativeDateIndex = Table.AddColumn(RelativeMonthLabel, "Relative Day Index", each Number.From( [FullDateAlternateKey] - [System Date] )),

        MonthYear = Table.AddColumn(RelativeDateIndex, "Month Year", each Date.ToText( [FullDateAlternateKey], "MMM yyyy" )),

        YearQrt = Table.AddColumn(MonthYear, "Year Qrt", each Text.From( [Year] ) & "-Q" & Text.From( [Calendar Quarter] )),

        #"Changed Type" = Table.TransformColumnTypes(YearQrt,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"Fiscal Year", Int64.Type}, {"Fiscal Quarter", Int64.Type}, {"Calendar Quarter", Int64.Type}, {"IsWeekDay", Int64.Type}, {"DayOfWeek", Int64.Type}, {"FullDateAlternateKey", type date}, {"DateKey", Int64.Type}, {"DateFullName", type text}, {"Month Name", type text}, {"Day of Week Name", type text}, {"Relative Year Index", Int64.Type}, {"Relative Month Index", Int64.Type}, {"Relative Day Index", Int64.Type}}),

        #"WHAT TO KEEP ---->" = Table.SelectColumns(#"Changed Type",{"Year", "Month", "Day", "FullDateAlternateKey", "DateKey", "DateFullName", "Fiscal Year", "Fiscal Quarter", "Calendar Quarter", "IsWeekDay", "DayOfWeek", "Day of Week Name", "Day of Week Name Short", "Month Name", "Month Name Short", "System Date", "Relative Year Index", "Relative Year Label", "Relative Month Index", "Relative Month Label", "Relative Day Index", "Month Year", "Year Qrt"}),

        #"Renamed Columns" = Table.RenameColumns(#"WHAT TO KEEP ---->",{{"DateFullName", "Text Date"}, {"FullDateAlternateKey", "Work Date"}})

    in

        #"Renamed Columns"

    ----------end------------



    ------------------------------
    Gregory Petrossian
    Business Transformation Consultant
    CSG Pro
    Portland OR
    gregp@csgpro.com
    ------------------------------



  • 5.  RE: DimDate tables

    Bronze Contributor
    Posted Apr 26, 2018 05:57 PM

    Hi Tate! Great to meet you last night, and thanks for participating in our community.

     

    Here's a link to the SQL BI Date Table option that was mentioned last night:

     

    https://www.sqlbi.com/tools/dax-date-template/

     

    You may not want to generate your table in DAX, but it is a viable option and the download from the Italians produces a pretty rich date table, complete with Fiscal options.

     






  • 6.  RE: DimDate tables

    Top Contributor
    Posted May 03, 2018 11:52 PM
    Hi Tate, 

    We use a custom calendar, so I actually create mine in my data mart, which is in SQL Server. 
    Then I use the Query Editor in Power BI to add any custom columns needed for measures, filters, etc. 
    The calendar table also supports SSRS reports and ETL processes, so it needs to in a database. 

    Our fiscal calendar is fixed - with 13 periods of 4 weeks each, and our week starts on Wednesday. 
    There is a "leap week" every 7 years, so that will be fun when it rolls around.
     
    I fill the calendar table using fairly straight forward SQL  - nothing super fancy, and and nothing that isn't reproduceable in DAX.
    It is just a pattern of 7 days, 4 weeks, and 13 periods so at least the math is easy. 

    For my current data models, the table does not contain any future dates. 
    Keeps the visualizations nice and clean, and makes custom time intelligence easier. 
    I do plan on having another calendar table that does though, for showing things like expected deliveries a week or two out. 

    And of course, all my time intelligence has to be custom.

    Hope that helps, 
    Audrey


    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    New Seasons Market
    Portland OR
    ------------------------------



  • 7.  RE: DimDate tables

    Microsoft Power Platform Team
    Posted May 17, 2018 02:51 PM

    Thanks for all the feedback on the "Calendar" and "DimDate" tables from everyone. I found all the info and links very helpful.

    I saw that the main PUG forum had a relevant discussion on using DAX or M for these tables... see the link below:

    PUG Exchange forum topic on DAX or M for calendars

    Thanks again, 

    Glad to be part of this community and share knowledge with experts and enthusiasts. 

    -Tate



    ------------------------------
    Tate Breswick
    Manufacturing Engineer
    Portland OR
    ------------------------------



  • 8.  RE: DimDate tables

    Posted Nov 05, 2018 01:49 PM
    Echoing one or two of the comments above, we have a full calendar saved in our SQL manufacturing system, though it was spread throughout a few tables. We have a view that joins it all together along with a collection of every day in the year for a few years so every day has the associated period, quarter, etc... Our calendar was a 4 week/5 week with the occasional "leap week" storing it this way became quite handy for time-based <g class="gr_ gr_502 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="502" data-gr-id="502">dax</g> calculations. These became necessary because of the strangeness of our fiscal calendar. 

    I would actually suggest as a first step to see if anyone has recorded the info on a database somewhere or even excel sheet that lists out all the dates or at least period start-end dates. With the later, you could at least join in each date by checking if it's between or equal to the start and end dates, assuming you have a good tool like SQL or something at your disposal to make a complete list.

    ------------------------------
    Jarrett Sauby
    Business Intelligence Developer
    Beaverton OR
    ------------------------------