cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hidenseek9 Member
Member

Re: dax calendar eomonth min/max

@v-jiascu-msft

 

How would the expression change if I have 3 tables with dates and

try to create a calendar with the minimum date of all 3 tables to

the maximum date of all 3 tables on a month level?

 

Many thanks,

 

H

Microsoft v-jiascu-msft
Microsoft

Re: dax calendar eomonth min/max

Hi @hidenseek9,

 

There is a wonderful function called "Calendarauto". Please try it. Reference: MAX-amp-MIN-Dates-from-Multiple-tables and https://msdn.microsoft.com/en-us/library/dn802534.aspx

DateTable =
CALENDARAUTO ()

Best Regards!

Dale

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

Re: dax calendar eomonth min/max

@v-jiascu-msft

 

I simply copied and pasted the formula.

However, it did not work.

I created a new table with below formula and it did not work.

 

Would I need to add below formula in each of the 3 tables that I have?

I have a difficult time understanding the formula below.

 

Please help.

 

Many thanks,

 

H

 

 

ADDCOLUMNS (
CALENDARAUTO(),
"Year", YEAR ( [Date] ),
"QuarterOfYear", "Q" & FORMAT ( [Date], "Q" ),
"MonthOfYear", FORMAT ( [Date], "MM" ),
"MonthInCalendar", FORMAT ( [Date], "mmm YYYY" ),
"QuarterInCalendar", "Q" & FORMAT ( [Date], "Q" ) & " " & FORMAT ( [Date], "YYYY" ),
"DayInWeek", WEEKDAY ( [Date] ),
"DayOfWeekName", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"RelativeYear", (YEAR(NOW())- YEAR ( [Date] )))

Microsoft v-jiascu-msft
Microsoft

Re: dax calendar eomonth min/max

Hi @hidenseek9

 

What's the error message? Are the data type of these date column "DateTime"?

 

Best Regards!

Dale

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

Re: dax calendar eomonth min/max

@v-jiascu-msft

 

Please find below as the error message.

Appreciate your support!

 

2017-09-15 11_41_29-CAB to CAF Dashboard - Power BI Desktop.png

 

H

Microsoft v-jiascu-msft
Microsoft

Re: dax calendar eomonth min/max

Hi @hidenseek9,

 

Please check these items below.

1. Are all your tables calculated tables, which are created through "New Table" button?

2. Are the data type of all the date columns "DateTime"? Can't be "Text".

 

If it's convenient for you, please provide the PBIX file.

 

Best Regards!

Dale

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

Re: dax calendar eomonth min/max

@v-jiascu-msft

 

Oh okay. A calculated table was the issue.

It works beautifully now!

Awesome!

 

Thanks,

 

H

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors