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
bwelsh
Helper I
Helper I

Dynamic Date Table for Last 13 Months

Hello, I am having some trouble creating a Date Dimension Table for the last 13 months. I tried using DateDimension = CALENDAR(MONTH(today())-13,today()), however this is showing me months in many years. How can I limit this to only the last 13 months from today?

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

Hi @bwelsh,

You can also create the date table using the DAX below.

DateTable = CALENDAR(DATE(YEAR(TODAY()),MONTH(TODAY())-13,DAY(TODAY())),today())

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yuezhe-msft
Employee
Employee

Hi @bwelsh,

You can also create the date table using the DAX below.

DateTable = CALENDAR(DATE(YEAR(TODAY()),MONTH(TODAY())-13,DAY(TODAY())),today())

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

and how will you do dynamic calendar table with 13 full months in past and 12 full months in future

Peter_R
Resolver II
Resolver II

There are a number of ways for creating a date table. I like the method of creating a general purpose one in the queries, using a function to generate the table that is called with start date/ end date parameters. This provides the best flexibility for creating tables based on min/max dates in the fact tables, or basing them on current dates. I came across this approach for creating the date table from Chris Webb, and have tweaked it somewhat.  Here is the code for the function, called CreateDateTable:

let
    CreateDateTable = (StartDate, EndDate) =>
let
    /*StartDate=#date(2016,1,1),
    EndDate=#date(2016,12,31),*/

    //Create lists of month and day names for use later on
    MonthList = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"},
    DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},

    //Find the number of days between the end date and the start date
    NumberOfDates = Duration.Days(EndDate-StartDate)+1,

    //Generate a continuous list of dates from the start date to the end date
    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
 
   //Turn this list into a table
    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    //Cast the single column in the table to type date

    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),

    //Add custom columns for day of month, month number, year
    DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),
    MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),
    Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),
    DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),

    //Since Power Query doesn't have functions to return day or month names, use the lists created earlier for this
    MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}),
    DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),
  
  //Add a column that returns true if the date on rows is the current date
    IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date])),

    ChangeTypes = Table.TransformColumnTypes(IsToday ,{{"Date", type date}, {"DayOfMonth", Int64.Type}, {"MonthNumberOfYear", Int64.Type}, {"Year", Int64.Type}, {"DayOfWeekNumber", Int64.Type}, {"IsToday", type logical}, {"DayName", type text}, {"MonthName", type text}})


in
    ChangeTypes
in
    CreateDateTable

To create the date table, you invoke the function from a blank query called whatever you wish to call the dim table. Here is how I would call it to create the 13 months from today table:

let
    #"MaxDate" = DateTime.Date(DateTime.LocalNow()),
    #"MinDate" = Date.AddMonths(#"MaxDate", -13),

    #"CallCreateDateTable" = CreateDateTable(MinDate, MaxDate)

in
    #"CallCreateDateTable"

Cheers

bwelsh
Helper I
Helper I

I was able to find a solution by using CALENDAR(MIN('DataSourceTable'[Date Column]),today()) which should work fine for what I'm trying to do, but I would still be interested to know how to do this another way.

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.