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

Exam DA-100 All Questions

View all questions & answers for the DA-100 exam
Go to Exam

Exam DA-100 topic 9 question 2 discussion

Actual exam question from Microsoft's DA-100
Question #: 2
Topic #: 9
[All DA-100 Questions]

HOTSPOT -
You need to calculate the last day of the month in the balance sheet data to ensure that you can relate the balance sheet data to the Date table.
Which type of calculation and which formula should you use? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer Hide Answer
Suggested Answer:
Box 1: A DAX Calculated measure -
Box 2: Date.EndofQuarter(#date([Year],[Mont],1))
ENDOFQUARTER returns the last date of the quarter in the current context for the specified column of dates.
The following sample formula creates a measure that returns the end of the quarter, for the current context.
= ENDOFQUARTER(DateTime[DateKey])
Reference:
https://docs.microsoft.com/en-us/dax/endofquarter-function-dax
Model the Data
by VR1 at Oct. 5, 2020, 3:09 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
VR1
Highly Voted 4 years, 8 months ago
Type of calculation - M Custom Column Formula - Date.EndOfMonth
upvoted 168 times
Fofo97
3 years ago
Thats is right bro!
upvoted 1 times
...
ade2ben
3 years, 11 months ago
Supported
upvoted 2 times
...
Huzak
4 years, 2 months ago
Agree, see reference https://docs.microsoft.com/en-us/powerquery-m/date-endofmonth
upvoted 5 times
...
...
dtTech
Highly Voted 4 years, 8 months ago
C and A
upvoted 22 times
...
PinkZebra
Most Recent 2 years, 8 months ago
It must be end of month, so Formula must include Endofmonth. There is only one Endofmonth formula which is a M formula, so it has to be an M custom column.
upvoted 2 times
...
TechDiva
3 years, 3 months ago
I got it on my exam 03/17/2022. 90-95% questions were from here. 61 questions in 100 min. All 3 case studies that appeared in exam, where from here.
upvoted 1 times
TechDiva
3 years, 3 months ago
Answered: M custom and EndOfMonth
upvoted 1 times
...
...
smario
3 years, 5 months ago
Correct Answer: A Scenario: At least one of the balance sheet reports in the quarterly reporting package must show the ending balances for the quarter, as well as for the previous quarter. DATESQTD returns a table that contains a column of the dates for the quarter to date, in the current context. Reference: https://docs.microsoft.com/en-us/dax/datesqtd-function-dax
upvoted 1 times
...
PatrickStr
3 years, 6 months ago
on exam 12/10/2021
upvoted 1 times
...
aguilartu1
3 years, 7 months ago
on exam - Nov 11, 2021. my answers: An M custom column Date.EndOfMonth
upvoted 1 times
...
kcwood94
3 years, 8 months ago
On exam 10/14/21
upvoted 1 times
...
jecapanda
3 years, 10 months ago
So why not DAX column?
upvoted 1 times
...
amine_bezzi
3 years, 12 months ago
We can't relate using Calculated measure
upvoted 1 times
...
D24G
4 years, 1 month ago
You can't relate using a measure? by the nature of measures they change, so wouldn't be able to provide a value to relate the tables on
upvoted 2 times
...
holySinner
4 years, 1 month ago
C and A;
upvoted 2 times
...
DeathCaliberKnight
4 years, 1 month ago
answer is correct
upvoted 2 times
Massy
4 years, 1 month ago
answer is wrong, because the formula indicated is an M formula...
upvoted 4 times
...
...
EmperorWenchang
4 years, 2 months ago
According to the answer provided by my Power BI instructor in Taiwan, Linda Wu（吳翠鳳）, the answer must be： An M custom column Date.EndOfMonth(#date([Year], [Month], 1))
upvoted 13 times
Billybob0604
3 years, 2 months ago
Linda Wu rules. Send her my greetings
upvoted 1 times
...
Zakriya
4 years, 1 month ago
So 2nd one must be A
upvoted 1 times
...
...
madrid99
4 years, 4 months ago
C & A. The balancesheet contains data for every month, it is not a quarterly fact
upvoted 3 times
...
JohnCrawford
4 years, 6 months ago
The reports are quarterly not monthly. I think the given answer works.
upvoted 1 times
Rastakilla
4 years, 6 months ago
are you sure on this as many evidence above indicates M and end ofMonth
upvoted 1 times
...
DubC
4 years, 5 months ago
The question specifically asks for end of month. The only formula that makes sense is the M syntax (Date.EndOfMonth), and thus the method is M. VR1's first response is correct IMO.
upvoted 3 times
...
...
CDL
4 years, 6 months ago
Tested. 3/1 M custom column + Date.EndOfMonth(#date([Year], [Month], 1))
upvoted 8 times
CDL
4 years, 6 months ago
Code FYI. let Source = Excel.CurrentWorkbook(){[Name="DateYearQ"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Year", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.EndOfMonth(#date([Year], [Month], 1))), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}) in #"Changed Type1"
upvoted 12 times
...
...
Load full discussion...
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