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 86 discussion

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

DRAG DROP -
You have a Power BI model that contains tables named Sales and Date. Sales contains four columns named SalesAmount, OrderDate, SalesPerson, and
OrderID.
You need to create a measure to calculate the last 12 months of sales. You must start from the last date a sale was made and ignore any filters set on the report.
How should you complete the DAX formula? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

Show Suggested Answer Hide Answer
Suggested Answer:
by TiaanR at July 10, 2019, 5:08 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
calibear
Highly Voted 5 years, 3 months ago
Last12MonthSales = var varlast12m = CALCULATE( DATEADD( LASTNONBLANK(Sales[OrderDate];SUM(Sales[SalesAmount]));-12;MONTH)) return CALCULATE(SUMX(FILTER(Sales;Sales[OrderDate]>varlast12m);Sales[SalesAmount]))
upvoted 13 times
...
123
Highly Voted 4 years, 10 months ago
Other insight: DATEADD, LASTNONBLANK https://www.kasperonbi.com/show-only-the-sales-for-the-last-12-months/
upvoted 7 times
...
CDL
Most Recent 4 years, 6 months ago
1. get the date 12 months ago: DateAdd 2. get the valid last date: LastNonBlank 3. nested above 2 together by “Calculate” https://www.kasperonbi.com/show-only-the-sales-for-the-last-12-months/ https://docs.microsoft.com/en-us/dax/dateadd-function-dax https://docs.microsoft.com/en-us/dax/lastnonblank-function-dax https://docs.microsoft.com/en-us/dax/calculate-function-dax
upvoted 3 times
...
TONYSOCCER
4 years, 7 months ago
can it be lastonblank first then dateadd?
upvoted 1 times
...
Vi1125
4 years, 7 months ago
Result is correct - to ignore any filters you could use ALL at the end - but the screen shot is only showing part of the code - DATEADD moves the time period, then LASTNONBLANK picks up last date where sales was made hence not blank - link below provided by 123 https://www.kasperonbi.com/show-only-the-sales-for-the-last-12-months/
upvoted 1 times
...
refrakt
4 years, 9 months ago
Everyone raising valid points but I think the reality is that the code section is only an extract. The use of var (https://docs.microsoft.com/en-us/dax/var-dax) suggests this is purely intended to pull the date required, for a later return step to then perform the required calculation. Always an issue I have with 'complete the code' question types as invariably they lose some of the context you want to be assured of the answer.
upvoted 2 times
FrdFrd
4 years, 4 months ago
Totally agree with you in regards to this question. I struggled too much with it because the goal was to "calculate the last 12 months of sales", not "a year before the date where the last sale was made"
upvoted 1 times
...
...
PowerLjubica
4 years, 11 months ago
The first argument of the Calculate function is an expression, and the first argument of the DATEADD is a Date column, this seems to be mixed up. Also, after VAR you have to type RETURN at the end.
upvoted 1 times
...
CYRUSD
5 years ago
varlast12m= Calculate(SUM(Sales[SalesAmount]), DATEADD( LASTNONBLANK(Sales[OrderDate], SUM(Sales[SalesAmount] ) ,-12 ,Month) What about this one?
upvoted 2 times
PowerBIconsultant12
4 years, 7 months ago
Did you test this formula? I count in your formula 5x brackets open "(" and only 3x brackers close ")" Therefore I say this will not work
upvoted 1 times
...
...
Dirk
5 years, 2 months ago
So - guys, like I suggested this question is completely broken, It's not possible to find the solution within this pattern. I found the same question with a different pattern elsewhere. The solution is this here: Last12monthSales= Var varlast12m= CALCULATE(LASTDATE (ALLEXCEPT (Sales[OrderDate], SUM(Sales[SalesAmount])),-12,MONTH), ALL(Sales))RETURNIF(Max(Date[Date]) >=varlast12m,SUM(Sales[SalesAmount])) Maybe let's discuss this instead
upvoted 4 times
...
lozqt
5 years, 2 months ago
Could not work out the correct answer ….
upvoted 2 times
...
Dirk
5 years, 2 months ago
So - from my point of view there is no chance to get the correct answer by the given pattern. DateAdd gives back a date. And Calculate(Date) can hardly result into a measure with an amount.
upvoted 2 times
...
TiaanR
5 years, 10 months ago
This will calculate the first date from where the measure should look at, but it does not calculate the total sales for the period.
upvoted 5 times
JohnFan
5 years, 5 months ago
DateAdd is a function that adds or subtracts a number of days/months/quarters/years from or to a date field. DateAdd can be used like this: DateAdd(<date field>, <number of intervals>, <interval>) https://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question
upvoted 3 times
JohnFan
5 years, 5 months ago
LASTNONBLANK(<column>,<expression>) Returns the last value in the column, column, filtered by the current context, where the expression is not blank. https://docs.microsoft.com/en-us/dax/lastnonblank-function-dax
upvoted 4 times
...
CYRUSD
5 years ago
Result of LASTNONBLANK(Sales[OrderDate],SUM(Sales[SalesAmount]) would be the date that last non blank sales amount is founded
upvoted 1 times
...
...
raspberry
5 years, 3 months ago
No, it won't even do that. To get the last date when sales occurs we need to use LASTNONBLANK ( Sales[OrderDate], CALCULATE( SUM( Sales[SalesAmount] ) ) ) . It requires wrapping the SUM with CALCULATE. https://exceleratorbi.com.au/lastnonblank-explained/ Without that wrap the the defined variable returns the date = 12 month before the last date in the Sales[OrderDate] regardless SalesAmount. So without the wrap, if your [OrderDate] ends in 3000-01-01, it will return 2999-01-01 (but imagine you got last sales only this year 2020! )
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