dax calendar eomonth min/max

[emveha]

hi all,

am trying to create a table with all dates of a year and did it like this:

yrCalendar= CALENDAR(EOMONTH(MIN('Treg'[ActivityDate]),-1)+1,EOMONTH(MAX('Treg'[ActivityDate]),0)) but i get an error message= The syntax for '.0' is incorrect. DAX(CALENDAR(EPOMONTH(MIN( etc etc))

Why do i get this message and what do i need to do to solve it.

thnx

Mrt

[gooranga1]

I just tried it on a test dataset here and it works okay

dCalendar02 = CALENDAR(EOMONTH(min(Query1[event_timestamp]),-1)+1,EOMONTH(max(Query1[event_timestamp]),0))

from your error message it would seem that that the error is because of a full stop before your zero rather than a comma in the final EOMONTH function?

[emveha]

again, it s strange that someone else just informed me that for him/her it works.....

this is my function:

dCalendar02 = CALENDAR(EOMONTH(min('Treg'[ActivityDate]),-1)+1,EOMONTH(MAX('Treg'[ActivityDate]),0))

what is strange is, is the fact that after i have entered my function, the comma next to the first ActivityDate is changed from a comma to a semi column....

dCalendar02 = CALENDAR(EOMONTH(min('Treg'[ActivityDate]);-1)+1, etccc

[v-jiascu-msft]

Hi @emveha,

It's hard for me to tell why the change happened. Formats in Power BI connect to "Formats" in the local computer. According to the error message "..syntax...", maybe it's something wrong with ";" or "," in the formula. (This picture just shows one example)

BTW, the formula works well.

Best Regards!

Dale

[emveha]

hi,

changed my regional settings but still the same error...

Any ideas that would help solving this?

thnx

emveha

[v-jiascu-msft]

@emveha

Hi Emveha,

Could you please post a snapshot about the error here?

Best Regards!

Dale

[emveha]

hope this will help...

[v-jiascu-msft · Accepted solution]

Hi @emveha,

Try this formula please.

dCalendar02 =
CALENDAR (
    EOMONTH ( MIN ( 'Time registration'[ActivityDate] ); -1 ) + 1;
    EOMONTH ( MAX ( 'Time registration'[ActivityDate] ); 0 )
)

Best Regards!

Dale

[hidenseek9]

@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

[v-jiascu-msft]

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

[hidenseek9]

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

[v-jiascu-msft]

Hi @hidenseek9, 

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

Best Regards!

Dale

[hidenseek9]

@v-jiascu-msft

Please find below as the error message.

Appreciate your support!

H

[v-jiascu-msft]

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

[hidenseek9]

@v-jiascu-msft

Oh okay. A calculated table was the issue.

It works beautifully now!

Awesome!

Thanks,

H

[vanessafvg]

@emveha works find for me are you creating a new table?

[emveha]

yes, want to create a new table.....