Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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?
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
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
hi,
changed my regional settings but still the same error...
Any ideas that would help solving this?
thnx
emveha
Hi Emveha,
Could you please post a snapshot about the error here?
Best Regards!
Dale
hope this will help...
Hi @emveha,
Try this formula please.
dCalendar02 = CALENDAR ( EOMONTH ( MIN ( 'Time registration'[ActivityDate] ); -1 ) + 1; EOMONTH ( MAX ( 'Time registration'[ActivityDate] ); 0 ) )
Best Regards!
Dale
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
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
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] )))
Hi @hidenseek9,
What's the error message? Are the data type of these date column "DateTime"?
Best Regards!
Dale
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
Oh okay. A calculated table was the issue.
It works beautifully now!
Awesome!
Thanks,
H
@emveha works find for me are you creating a new table?
Proud to be a Super User!
yes, want to create a new table.....
User | Count |
---|---|
94 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |