HOTSPOT -
You have a Power BI model that contains a table named Sales and a related date table. Sales contains a measure named Total Sales.
You need to create a measure that calculates the total sales from the equivalent month of the previous year.
How should you complete the calculation? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Box 1: CALCULATE -
Box 2: PARALLELPERIOD -
PARALLELPERIOD returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time.
Syntax: PARALLELPERIOD(
Incorrect:
SAMEPERIODLASTYEAR returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.
Syntax: SAMEPERIODLASTYEAR(
DATESMTD returns a table that contains a column of the dates for the month to date, in the current context.
Syntax: DATESMTD(
Box 3: 'DATE' [Month]
Reference:
https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax
CALCULATE SAMEPERIODLASTYEAR 'DATE'[DATE]
Correct! PARALLELPERIOD needs 3 arguments and it returns the sales for the entire year Reference: https://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question
Correct: See this video for more info: https://www.youtube.com/watch?v=dBSOYxyRR_w
This would return the sales on that single date of last year. You need date[month] to get the sales for the entire month of last year.
no. date([month]) will throw an error as the function expects a DATE format and month is integer(or string if format mmm). It MUST be date[Date]
There's a Sum missing. CALCULATE(SUM(), SAMEPERIODLASTYEAR(Date[Date]))
Nevermind, Sum isn't necessary as Total Sales is a measure not a column and already calculates the Sum.
Calculate SamePeriodLastYear 'Date'[Month] ParallelPeriod could work but here the second agrument only takes one parameter and ParallelPeriod requires three
SAMEPERIODLASTYEAR accepts a data column, Month will usually be either text (Jan) or Integer (1). so: CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Yup! This is correct!!
Which one is the correct answer is it 'Date[Month] or 'Date'[Date] as it has got equal number of votes so confused.
I guess 'Date'[Date]. If you look up for the SAMEPERIODLASTYEAR DAX, it has a Date parameter.
You need date[month] to get the sales for the entire month of last year.
Exam: 15/10/2023 Score: 948/1000 Answer: Calculate,Sameperiodlastyear,Date[Date]
CONGRATULATIONS!! CAN I GET UR DETAILS SO THAT I CAN ASK U ABOUT EXAM
Just one question, why there are so many "correct answers" which are actually not correct on this website? Most of the time I need to check the discussions to find the right answer.
28-Mar-24 - This Q came up. Blitzed all 266 Q's on Exam Topics and Scored 813/1000. Only 1 question out of the 50 wasn't on exam topics exactly.
Hi, you mean that almost all question for exam came from examtopics? I have fuew days to exam. Thanks
CALCULATE is used to change the context in which the data is evaluated. [Total Sales] is your existing measure that calculates total sales. SAMEPERIODLASTYEAR('Date'[Date]) shifts the context of the date by one year, allowing the calculation to be made for the same period last year. This measure will give you the total sales for the same month in the previous year. Make sure that your date table ('Date') is marked as a date table and properly related to your Sales table.
It needs to be 'DATE'[DATE] because SAMEPERIODLASTYEAR only accepts Date columns as arguments. If you try to input the Month column the following error is prompted: "MdxScript(Model) (216, 5) Calculation error in measure •_ProjectMeasures'[Measure Last Year]: A column specified in the call to function 'SAMEPERIODLASTYEAR' is not of type DATE. This is not supported."
AS per Microsoft Definition found in: https://learn.microsoft.com/en-us/dax/sameperiodlastyear-function-dax Example The following sample formula creates a measure that calculates the previous year sales of Reseller sales. DAX: LastYearSales= CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[DateKey]))
The correct : 1) CALCULATE 2) SAMEPERIODLASTYEAR 3) 'DATE'[DATE] The fonction accepte 3 param like this PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)
The Suggested Answers are so ridiculous. I answer correctly and later get irritated that again wrong answers under 'Show Suggested Answer'. Mine: CALCULATE SAMEPERIODLASTYEAR 'DATE'[DATE]
It is correct, I think it should be Calculate , Sameperiodlastyear,Date[Month] as they have asked in the question to find the total sales from the equivalent month of the previous Year.
Calculate, Sameperiodlastyear and `Date`[Date] are the answers.
Calculate sameperiolastyear 'DATE'[DATE] Does it need a grouping function like SUM around the [TotalSales]? Otherwhise how can it group them? Or it is only to filter and obtain for a row with a particular data the same the last year?
Have tried to build the measures. It woks with the following: 1. SAMEPERIODLASTYEAR(<dates>) Sales Previous Year = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(Date[Date])) 2. PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>) Sales Previous Year = CALCULATE([Total ales],PARALLELPERIOD(Date[Date],-12,MONTH)) 3. DATEADD(<dates>,<number_of_intervals>,<interval>) Sales Previous Year = CALCULATE([Total Sales],DATEADD(Date[Date],-12,MONTH))
The data recording frequency is not specified, but based on the snap, we should assume that the data is recorded on a monthly basis. In accordance with this assumption, the syntax using PARALLELPERIOD AND SAMEPERIODLASTYEAR would be: 1. CALCULATE(SUM('Table Name'[Total Sales]), PARALLELPERIOD('Table Name'[Date], -12, Month) 2.CALCULATE(SUM('Table Name'[Total Sales]), SAMEPERIODLASTYEAR('Table Name'[Date])) Given only one argument in the final box, this is the correct answer - CALCULATE, SAMPERIODLASTYEAR, DATE[DATE]
CALCULATE SAMEPERIODLASTYEAR 'DATE'[DATE] / 'DATE'[MONTH]
Answer is wrong and misleading. Correct answers: CALCULATE SAMEPERIODLASTYEAR 'DATE'[DATE] We should use SAMEPERIODLASTYEAR because it as a function would calculate the expression for the previous year period to the period in filter context. Also, we should input the 'DATE'[DATE] column for references the previous year period.