Forum Discussion
Helper IDate Dimension Table that Dynamically Pulls Start and End dates from a Column of Dates
Hi
I'm trying to create a date dimension table which is dynamic and uses the dates from two different tables to determine the end and start date.
When I use the below measure to determine the earliest date across two tables it returns the date 30/01/2017.
Measure = MIN(MINX('Programme Resource Planning','Programme Resource Planning'[Date]), MINX('Project Hours Booked','Project Hours Booked'[Date]))
When I use the same text in the below calculated table it starts with date 01/04/2017. I have no idea why. What am I doing wrong? I have a similar issue with the end date for the table.
DateTable =
ADDCOLUMNS (
CALENDAR (MIN(MINX('Programme Resource Planning','Programme Resource Planning'[Date]), MINX('Project Hours Booked','Project Hours Booked'[Date])),
MAX(MAXX('Programme Resource Planning','Programme Resource Planning'[Date]), MAXX('Project Hours Booked','Project Hours Booked'[Date]))),
"Year", YEAR ( [Date] ),
"MonthOfYear", FORMAT ( [Date], "MM" ),
"QuarterOfYear", FORMAT ( [Date], "Q" ),
"WeekNo", WEEKNUM( [Date], 2),
"DateInt", FORMAT ( [Date], "YYYYMMDD" ),
"MonthName", FORMAT ( [Date], "mmmm" ),
"MonthInCalendar", FORMAT ( [Date], "mmm YYYY" ),
"QuarterInCalendar", "Q" & FORMAT ( [Date], "Q" ) & " " & FORMAT ( [Date], "YYYY" ),
"DayInWeek", WEEKDAY ( [Date] ),
"DayOfWeekName", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ))
Thanks, I appreciate you taking a look.
Based upon what you said I had a further play. I removed the add new columns part of the formula (see below), this gave me the correct results.
I then started adding the new columns a column at a time. Turns out it the data was being sorted by the last column being added so in the final table it was sorting by date name (April 1st was the first date, September 30th was the last date!) Feel a bit stupid but thanks for helping!
Test Calender = CALENDAR
(MIN
( MINX('Programme Resource Planning','Programme Resource Planning'[Date]), MINX('Project Hours Booked','Project Hours Booked'[Date])),
MAX(MAXX('Programme Resource Planning','Programme Resource Planning'[Date]), MAXX('Project Hours Booked','Project Hours Booked'[Date])))
3 Replies
v-huizhn-msftMicrosoft Employee
Hi dan_yoxall,
I create sample table, and get min date and max date from different tables.Mindate = MIN(MINX(Table1,Table1[Date]),MINX('Table 2','Table 2'[Date])) Maxdate = MAX(MAXX(Table1,Table1[Date]),MAXX('Table 2','Table 2'[Date]))Create two card visuals to display the min and max date.
Use your formula to get expected result as follows, all things works correctly. Date is from 2014/1/1 to 2014/3/2.
For your issue, it's weird, please check if there is other filter on the resource tables(Programme Resource Planning','Project Hours Booked').
Best Regards,
Angelia
dan_yoxallThanks, I appreciate you taking a look.
Based upon what you said I had a further play. I removed the add new columns part of the formula (see below), this gave me the correct results.
I then started adding the new columns a column at a time. Turns out it the data was being sorted by the last column being added so in the final table it was sorting by date name (April 1st was the first date, September 30th was the last date!) Feel a bit stupid but thanks for helping!
Test Calender = CALENDAR
(MIN
( MINX('Programme Resource Planning','Programme Resource Planning'[Date]), MINX('Project Hours Booked','Project Hours Booked'[Date])),
MAX(MAXX('Programme Resource Planning','Programme Resource Planning'[Date]), MAXX('Project Hours Booked','Project Hours Booked'[Date])))
- v-huizhn-msft
Hi dan_yoxall,
Congratulations, you have resolved your issue, please mark the right reply as answer, so other people can fine workaround clearly.
Thanks,
Angelia