cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dan_yoxall Helper I
Helper I

Date 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" ))

1 ACCEPTED SOLUTION

Accepted Solutions
dan_yoxall Helper I
Helper I

Re: Date Dimension Table that Dynamically Pulls Start and End dates from a Column of Dates

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])))

View solution in original post

3 REPLIES 3
Microsoft v-huizhn-msft
Microsoft

Re: Date Dimension Table that Dynamically Pulls Start and End dates from a Column of Dates

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.

1.PNG

Use your formula to get expected result as follows, all things works correctly. Date is from 2014/1/1 to 2014/3/2.

2.PNG

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

Re: Date Dimension Table that Dynamically Pulls Start and End dates from a Column of Dates

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])))

View solution in original post

Microsoft v-huizhn-msft
Microsoft

Re: Date Dimension Table that Dynamically Pulls Start and End dates from a Column of Dates

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors