ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-778 All Questions

View all questions & answers for the 70-778 exam
Go to Exam

Exam 70-778 topic 1 question 41 discussion

Actual exam question from Microsoft's 70-778
Question #: 41
Topic #: 1
[All 70-778 Questions]

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
Start of repeated scenario.
You have a Microsoft SQL Server database that has the tables shown in the Database Diagram exhibit. (Click the Exhibit button.)

You plan to develop a Power BI model as shown in the Power BI Model exhibit. (Click the Exhibit button.)

You plan to use Power BI to import data from 2013 to 2015.
Product Subcategory[Subcategory] contains NULL values.
End of Repeated Scenario.
You implement the Power BI model.
You plan to add a table named Date to the model. The table will have columns for the date, year, month, and end of the last month, and will include data from
January 1, 2013 to December 31, 2015.
The Date table and the Sales table will have a relationship.
Which DAX functions should you use to create the columns?

  • A. CALENDARAUTO, YEAR, MONTH, and EOMONTH
  • B. CALENDAR, YEAR, MONTH, and ENDOFMONTH
  • C. CALENDARAUTO, YEAR, MONTH, and ENDOFMONTH
  • D. CALENDAR, YEAR, MONTH, and EOMONTH
Show Suggested Answer Hide Answer
Suggested Answer: D 🗳️
References:
https://msdn.microsoft.com/en-us/query-bi/dax/calendar-function-dax https://msdn.microsoft.com/en-us/query-bi/dax/year-function-dax https://msdn.microsoft.com/en-us/query-bi/dax/month-function-dax https://msdn.microsoft.com/en-us/query-bi/dax/eomonth-function-dax
by Brunobsv at Oct. 11, 2019, 8:55 a.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Brunobsv
Highly Voted 5 years, 6 months ago
CALENDARAUTO could never take place in this situation, because its calculates the first and the last dates in the whole dataset, since we want the dates to be calculated in a specific period we have to use calendar. So the real question its wether one has to use EOMONTH or ENDOFMONTH. EOMONTH allows users to specify their data frame, so thats the right answer.
upvoted 17 times
JohnFan
5 years, 4 months ago
In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011. CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2011. CALENDARAUTO(3) will return all dates between March 1, 2010 and February 28, 2012.
upvoted 1 times
...
Amit01
5 years, 4 months ago
But the model itself collecting the data from 2013 to 2015, So isnt it good to use CalenderAuto??
upvoted 5 times
...
lozqt
5 years, 2 months ago
Yes but you already frame your dataset with Calendar at the begining hence either you use EOMONTH or ENDOFMONTH is the same to me. As you said the only added value of EOMONTH is that you can set dates which are different from the ones you set in CALENDAR. For this task it is not useful according to me ….
upvoted 1 times
CYRUSD
4 years, 11 months ago
it s looking for "end of the last month" not "end of the month,"
upvoted 3 times
Echicken69
4 years, 10 months ago
Agreed. EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. The DAX returns the date in datetime format of the last day of the month, before or after a specified number of months.
upvoted 1 times
Echicken69
4 years, 10 months ago
In addition, you can literally just use the PREVIOUSMONTH() function which returns a table that contains a column of all dates from the previous month....but that is not an option in this scenario.
upvoted 1 times
...
...
...
...
...
zalati
Most Recent 2 years, 6 months ago
Selected Answer: D
can any one give us the right dax code to create this
upvoted 2 times
...
CDL
4 years, 6 months ago
D is correct. 1. CALENDAR instead of CALENDARAUTO is because we need to specify Jan 1 to Dec 31 frm 2013 to 2015. What if auto may give u Jan something else. 2. EOMONTH instead of EndOfMonth is because we EOM returns a date, EndofM return a table. https://docs.microsoft.com/en-us/dax/calendar-function-dax https://docs.microsoft.com/en-us/dax/calendarauto-function-dax https://docs.microsoft.com/en-us/dax/eomonth-function-dax https://docs.microsoft.com/en-us/dax/endofmonth-function-dax
upvoted 3 times
...
da_boston
4 years, 6 months ago
I have to report myself wrong; I didn't read the question thoroughly; my answer is inaccurate. My subsequent research shows that D is correct answer.
upvoted 1 times
...
da_boston
4 years, 6 months ago
Reproduce this code and you will have the solution: "Date = CALENDAR(DATE(2013,1,1), DATE(2015,12,31))"
upvoted 2 times
...
Martin_Nbg
4 years, 7 months ago
We load data from 2013 to 215 and we want date table for 2013 to 2015, whole calendar years. Since CALENDARAUTO always generates whole years A is a valid answer. https://docs.microsoft.com/de-de/dax/calendarauto-function-dax
upvoted 2 times
FrdFrd
4 years, 3 months ago
I agree. Both "A" and "D" are valid answers. If the model is not yet populated, "A" wouldn't do the job. Only in that scenario "D" would be the only correct answer, but the text is not clear about that. If I had to choose between A and D I would choose D as it is the safest option
upvoted 1 times
...
...
cromastro
4 years, 8 months ago
The answer is D and you use -1 as second argument in EOMONTH.
upvoted 2 times
...
Luukvg
5 years, 1 month ago
Couldn't it be done with 1) CALENDARAUTO() and ENDOFMONTH(date) 2) CALENDAR("1-1-2010","12-31-2013") and EOMONTH(date,0) in which 1) is most simple?
upvoted 2 times
JideID
4 years, 11 months ago
CALENDAR allows you set your dates while CALENDARAUTO generates dates for you automatically. So, base on the given question, since Start and End dates were given, you should use CALENDAR
upvoted 4 times
...
pbia
4 years, 9 months ago
You cannot fixed the date range using CALANDERAUTO()
upvoted 2 times
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago