Dynamic Date Dimension setting max year to current year

[moeconsult]

I need help creating a Dimdate with start year of 2012 and I want Max year to always be the current year, i.e 2020 would the max for based on date column in my factable [Booking Date] .

Any help will be appreciated .

[parry2k]

@moeconsult try something like this:

Calendar = 
VAR __m = YEAR( MAX ( Table1[Date] ) ) 
RETURN 
CALENDAR ( "2012,1,1", DATE ( __m, 12, 31 ) ) 

[moeconsult]

This is giving me up to year 5000. and the Max booking date in my facttable is 21/03/2020 .

I had like to create a proper Datedimension table with Year,month,monthname, quater,week of year, week of month,day,day of week,day of year, day name. I think I can just date function to create this additional columns?

@parry2k 

[parry2k]

@moeconsult not sure why, did you tested the solution provided by others, that should end your calendar till the end of the last date in your fact table. That would be the first place to check.

Also if you drop date from facttable in a card visual and select latest in the aggregation, what date you get?

[moeconsult]

Looks like there is a problem with my date from the fact table. I have dimdate created using the code below:

I have Parameter for Start year 2012 and endyear 2020

let

    StartDate = #date(StartYear,1,1),

    EndDate = #date(EndYear,12,31),

    NumberOfDays = Duration.Days( EndDate - StartDate ),

    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),

    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),

    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),

    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([FullDateAlternateKey]), type number),

    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),

    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([FullDateAlternateKey]), type number),

    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),

    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),

    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number),

    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number),

    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),

    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text),

    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Day Name",{{"FullDateAlternateKey", type datetime}})

in

    #"Changed Type1"

Can I have make this Dynamic so that latest year in my Dimdate table will always be the current year?

@parry2k 

Thanks

[v-lionel-msft · Accepted solution]

Hi @moeconsult ,

Or like this?

Dimdate = 
VAR x = YEAR( TODAY() )
RETURN
CALENDAR(
    DATE( 2012, 1, 1),
    DATE( x, 12, 31 )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Perhaps:

Dimdate = CALENDAR(DATE(2012,1,1),MAX('factable'[Booking Date]))

?

[moeconsult]

This is giving me up to year 5000. and the Max booking date in my facttable is 21/03/2020 .

I had like to create a proper Datedimension table with Year,month,monthname, quater,week of year, week of month,day,day of week,day of year, day name. I think I can just date function to create this additional columns?

[camargos88]

Hi @moeconsult ,

Try creating a table like:

DimDate = CALENDAR(DATE(2020; 1;1); MAX(factable [Booking Date]))

Ricardo