Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dbrandone
Helper III
Helper III

Date Table Power Query adjustment for End Date

Hi Everyone, 

 

I have the below power query running my date table. I am pulling the start date from a specific column in the data and it happens to be 1/1/2018 which is good, but I set it up where the end date is the Max date from another column in a specific table. I think not having the end date always be 12/31 of a year may be throwing off my time intelligence measures using TotalYTD etc.

 

I do not have much expierience in Power query and was wanting to see if someone has an idea of how to have my EndDate value take the max year in the data but take it all the way to 12/31. Therefore if the column that max date pulls from has a date of 1/2/2022, then the date table would run all the way to 12/31/2022. 

 

First, would time intelligence be thrown off if I do not have the beginning of the date table start at 1/1 and the last date be 12/31?

 

If so, any ideas?

 

Current Power Query:

let
    Today=Date.From(DateTime.LocalNow()), // today's date
    StartDate = List.Min(Table.Column(vDonorFile, "ReferredOn (DateOnly)")),
    EndDate = List.Max(Table.Column(v_EyeDist_Request,"SurgeryDateDup")),
    StartofFiscalYear=7, 
    firstDayofWeek=Day.Monday,

    Source=List.Dates(
        StartDate,
        Duration.Days(EndDate-StartDate)+1,
        #duration(1,0,0,0)
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),
    FiscalMonthBaseIndex=13-StartofFiscalYear,
    adjustedFiscalMonthBaseIndex=if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
    #"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FiscalBaseDate", type date}}),
    #"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),
    #"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),
    #"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"FiscalBaseDate"}),
    #"Inserted Age" = Table.AddColumn(#"Removed Columns", "Age", each [Date]-Today, type duration),
    #"Extracted Days" = Table.TransformColumns(#"Inserted Age",{{"Age", Duration.Days, Int64.Type}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Extracted Days",{{"Age", "Day Offset"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "Month Offset", each (([Year]-Date.Year(Today))*12)
+([Month]-Date.Month(Today))),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Month Offset", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Year Offset", each [Year]-Date.Year(Today)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Year Offset", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Quarter Offset", each (([Year]-Date.Year(Today))*4)
+([Quarter]-Date.QuarterOfYear(Today))),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Quarter Offset", Int64.Type}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type4",{{"Day Offset", Order.Ascending}})
in
    #"Sorted Rows"

 

7 REPLIES 7
lbendlin
Super User
Super User

The calendar table has to be contiguous, and it has to cover both the fact dates and the result of the time intelligence calculations.  So cutting off future dates is fine unless you try to do stuff like "next month's data".

@lbendlin 

 

That is what I am finding. I didn't want to set the date table to a specific max year since in the future that max date may come up so I tied max date to a specific column in a table that deals with future orders. The problem is that it seems some time intelligence calculations ar off. I was wanting to see if there is a mixture of the two to keep the max date fluid, but also taking the max date in the specific column and taking it to the end of the calendar year. I feel comfortable with Dax, but with power query I am still relatively new.

 

any ideas on power query code to take the max date of a column and taking it to the end of the year?

Look at this from a different perspective:  If you disable auto date/time (as you should) then you save much more space in the data model than what you spend by specifying a fixed calendar that covers a couple of years in the future (say, until December 2023).

 

What you don't want to do is a CALENDARAUTO() on a date range from 1/1/1900 to 12/31/9999  (yep, seen that more than once, and the results are horrifying)

@lbendlin 

 

Absolutely. I definitely do not want to have many years in the future as part of my date table but for example, if my max date in the table column is 11/17/2021, then would my time intelligence work better if the power query then took the max date to 12/31/2021 since that is the last date in the year from the max date in the table column. Once a date in 2022 is entered into the table column, then the date table would go to 12/31/2022. 

the only reason I am wondering is seeing if having the max date as not 12/31 of a specified year, would this throw off say Startofyear time intelligence?

Keep in mind that fiscal years often do not end on 12/31. So no, it's not required. But it won't hurt either.

@lbendlin 

so do you know of anything I can add to the List.Max function for end date that will take my max date and take it to the last calendar date of the max year from my column?

If your fact dates are clean (no crazy dates) then CALENDARAUTO() will give you that for free.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.