Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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
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
and how will you do dynamic calendar table with 13 full months in past and 12 full months in future
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |