cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fraita
Frequent Visitor

Increase days on aldready existing DimDate-table

Hi,

I have this already existing table (I haven't made it) that I need to increase the years on. Right now it just goes to today's date, but I need it to increase to 2025-12-31

What should i change in the code? 

 

 

 

 

let

    Source = List.Dates,
    #"Invoke function" = Source(#date(2021, 01, 01), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())- #date(2021, 01, 01))+1, #duration(1, 0, 0, 0)),
  
    #"List to table" = Table.FromList(#"Invoke function", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    Date = Table.RenameColumns(#"List to table",{{"Column1", "Date"}}),
 
    #"Day Added" = Table.AddColumn(Date, "DayNo", each Text.PadStart(Number.ToText(Date.Day([Date])),2,"0")),
  
    #"Day Name Added" = Table.AddColumn(#"Day Added", "Day Name", each Date.ToText([Date],"ddd","en-US")),
    #"Month No Added" = Table.AddColumn(#"Day Name Added", "Month No", each Date.Month([Date])),

    #"Month Name Added" = Table.AddColumn(#"Month No Added", "Month Name", each Date.ToText([Date],"MMM","en-us")),

    #"Quarter No Added" = Table.AddColumn(#"Month Name Added", "Quarter No", each Date.QuarterOfYear([Date])),

    #"EUROPEAN Week No added" = Table.AddColumn(#"Quarter No Added", "WeekNo", each Text.PadStart(Number.ToText(Date.WeekOfYear([Date])+1),2,"0")),

    #"Year Added" = Table.AddColumn(#"EUROPEAN Week No added", "Year", each Date.Year([Date])),

    #"Year Month Added" = Table.AddColumn(#"Year Added", "Year-Month", each Number.ToText([Year])&"-"&[Month Name]),
    #"Year Week Added" = Table.AddColumn(#"Year Month Added", "Year-Week", each Number.ToText([Year])&"-"&[WeekNo]),

    #"Year Quarter Added" = Table.AddColumn(#"Year Week Added", "Year-Quarter", each Number.ToText([Year]) & "Q"& Number.ToText([Quarter No],"00")),

    #"Change type to text" = Table.TransformColumnTypes(#"Year Quarter Added",{{"Year", Int64.Type}, {"Date", type date}, {"Month No", Int64.Type},  {"Day Name", type text}, {"Month Name", type text}, {"Quarter No", Int64.Type}, {"Year-Quarter", type text}, {"Year-Month", type text}, {"WeekNo", Int64.Type}}),

    #"Sort DayName" = Table.AddColumn(#"Change type to text", "SortDayName", each Date.DayOfWeek([Date],1)),

    #"Sort YearMonth" = Table.AddColumn(#"Sort DayName", "SortYearMonth", each Number.ToText([Year])&Text.PadStart(Number.ToText([Month No]),2,"0")),
    #"Sort YearWeek" = Table.AddColumn(#"Sort YearMonth", "Sort YearWeek", each Number.ToText([Year])&Text.PadStart(Number.ToText([WeekNo]),2,"0")),

    #"Sort YearQuarter" = Table.AddColumn(#"Sort YearWeek", "SortYearQuarter", each Number.ToText([Year])&Text.PadStart(Number.ToText([Quarter No]),2,"0")),

    #"Changed Type to NO" = Table.TransformColumnTypes(#"Sort YearQuarter",{{"SortYearMonth", Int64.Type}, {"SortYearQuarter", Int64.Type}, {"SortDayName", Int64.Type}, {"Sort YearWeek", Int64.Type}, {"Year-Week", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type to NO",{{"Month Name", "Month"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "CurrentMonth", each Date.IsInCurrentMonth([Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "IsFuture", each Date.From(DateTime.LocalNow()) <= [Date]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"CurrentMonth", type logical}, {"IsFuture", type logical}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "CurrentYear", each Date.IsInCurrentYear([Date])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"CurrentYear", type logical}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type1", "Period", each (Date.Month(DateTime.LocalNow())-Date.Month([Date]) ) + 
(Date.Year(DateTime.LocalNow())-Date.Year([Date]) ) *12),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"Period", Int64.Type}})
in
    #"Changed Type2"

 

 

 

 

 
Thanks!

1 ACCEPTED SOLUTION

My bad for not reading your post more closely. Try this instead.

 

#"Invoke function" = Source(#date(2021, 01, 01), Duration.Days(#date(2025,12,31) - #date(2021, 01, 01))+1, #duration(1, 0, 0, 0)),

 

Pat

Microsoft Employee

View solution in original post

4 REPLIES 4
ppm1
Solution Sage
Solution Sage

Just add Date.EndOfYear in your second step as follows

 

#"Invoke function" = Source(#date(2021, 01, 01), Duration.Days(Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow()))- #date(2021, 01, 01))+1, #duration(1, 0, 0, 0)),

 

Pat

 

Microsoft Employee
Fraita
Frequent Visitor

I got it to 2022-12-31 but not 2025?

My bad for not reading your post more closely. Try this instead.

 

#"Invoke function" = Source(#date(2021, 01, 01), Duration.Days(#date(2025,12,31) - #date(2021, 01, 01))+1, #duration(1, 0, 0, 0)),

 

Pat

Microsoft Employee
Fraita
Frequent Visitor

Thank you so much!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors