Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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" ))
Solved! Go to Solution.
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])))
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
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])))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |