You have a Power BI model for sales data. You need to create a measure to calculate the year-to-date sales and to compare those sales to the previous year for the same time period. Which DAX function should you use?
Read here: https://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question
In short, parallelperiod can never be right. Parallelperiod returns sales for entire last year. Sameperiodlastyear returns sales for the same period last year.
B. SAMEPERIODLASTYEAR is the correct answer.
YTD can't be calculated using PARALLELPERIOD or PREVIOUSYEAR
Simply if I want to calculate the YTD sales and the current date is 15/07/2019
PARALLELPERIOD will give the last year sales up to 31/07/2018
PREVIOUSYEAR will give me the last year up to 31/12/2018
Yes B. SAMEPERIODLASTYEAR is the correct answer.
but PARALLELPERIOD and PREVIOUSYEAR both of them give us the last year from 1/1/2018 up to 12/31/2018. So you can filter them by year not with the date.
C is the correct answer.
When we read the question "You need to create a measure to calculate the year-to-date sales and...." first we need to use DATESYTD.
C.
from Ref:
[Prev Yr YTD Sales] =
CALCULATE([Total Sales], DATEADD(DATESYTD(Calendar[Date]),-1,Year))
From TotalYTD example:
The following sample formula creates a measure that calculates the 'year running total' or 'year running sum' for Internet sales.
= TOTALYTD(SUM(InternetSales_USD[SalesAmount_USD]),DateTime[DateKey])
the problem with PARALLELPERIOD is "PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns."
Hence if we are comparing "to date" numbers, PARALLELPERIOD may provide an incorrect comparision.
We need to use DATESYTD first, then SAMEPERIODLASTYEAR or DATEADD.
Finally someone who understand the guestion and the solution at the same time (bravo r8d1). The correct solution for this scenario would be (for example):
1. YTDSalesAmount = CALCULATE(sum(FactInternetSales[SalesAmount]), DATESYTD(DimDate[FullDateAlternateKey]))
2. YTDSalesAmoutnLY_DATEADD = CALCULATE([YTDSalesAmount], DATEADD(DimDate[FullDateAlternateKey], -1, year))
or
YTDSalesAmoutnLY_SAMEPERIODLASTYEAR = CALCULATE([YTDSalesAmount], SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))
We cannot complete this with PARALLELPERIOD and PREVIOUSYEAR. Both function return all year YTD sale. We need YTD sale to the previous year for the same time period.
Function SAMEPERIODLAST YEAR doesn't exist, but only SAMEPERIODLASTYEAR. There must be a trick.
So, correct answer is C, DATESYTD.
Remember, we don't have measure to calculate YTD sale yet. So, we must create it first, then use it with function DATEADD or SAMEPERIODLASTYEAR.
The following sample formula creates a measure that calculates the previous year sales for Internet sales.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey],-1,year))
I think they wanted to ask which particular function you can use to create both measures for current and previous year, and PARALELPERIOD is the one. This only has sense to me.
Im going with DATESYTD... It seems this question wants you to calculated YTD sales first before comparing those to previous year for the same time period. PARALLELPERIOD, SAMEPERIODLASTYEAR, and PREVIOUSYEAR can all get you the same result. DATESYTD is the only one that will get you YTD sales...
If I had to write a measure with ONE function, it would be PARALLELPERIOD (with parameter -1 for last year, and 0 for this year).
So the answer is A.
DATESYTD (or TOTALYTD) alone doesn't calculate the previous year, and SAMEPERIODLASTYEAR itself doesn't calculate the current year.
Besides, I'd normally write a measure using both TOTALYTD and SAMEPERIODLASTYEAR. That gives 2 functions. There is no such answer.
creates a measure that calculates the 'Running Total' for Internet sales. =CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESYTD(DateTime[DateKey]))
creates a measure that calculates the previous year sales of Reseller sales =CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[DateKey]))
In #84, you already "create a measure to calculate the year-to-date sales", so what leftover is "to compare the year-to-date sales with the previous year for the same time period", you thus use SAMEPERIODLASTYEAR.
In this question, you need to "create a measure to calculate the year-to-date sales", as well as "to compare the year-to-date sales with the previous year for the same time period". So you can CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey],-1,year/Month)) and CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey], 0, year/Month))
ParallelPeriod only works on month, quarter, and year
https://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question
creates a measure that calculates the previous year sales for Internet sales = =CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey],-1,year))
Yes, but in the documentation of PREVIOUSYEAR you find the same example using PREVIOUSYEAR.
upvoted 1 times
...
...
...
...
...
This section is not available anymore. Please use the main Exam Page.70-778 Exam Questions
Log in to ExamTopics
Sign in:
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.
twane
Highly Voted 5 years, 8 months agoJohnFan
5 years, 5 months agomohroshdy
Highly Voted 5 years, 4 months agoSeyed
5 years, 3 months agoLhouss
Most Recent 4 years, 5 months agoCDL
4 years, 6 months agor8d1
4 years, 10 months agoborut
4 years, 9 months agoEchicken69
4 years, 10 months agoPowerLjubica
4 years, 11 months agoCYRUSD
4 years, 11 months agoCYRUSD
4 years, 11 months agoRicky7876
5 years, 2 months agoRutRut
5 years, 2 months agoJohnFan
5 years, 5 months agocoupet
5 years, 4 months agoemile
5 years, 6 months agoJohnFan
5 years, 5 months agoJohnFan
5 years, 5 months agoalexhuang
5 years, 10 months agoexam_taker5
5 years, 10 months agorhsdeal
5 years, 8 months agoJohnFan
5 years, 5 months agoMartin_Nbg
4 years, 8 months ago