Please can somebody explain to me how Sales Var % figure is 2.74%
These are the values I got from the doing the LAB 2
Total Sales = SUM(Sales[Revenue]) =5,127,755,449.65
LY Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date])) =4,877,019,942.08
Sales Var = [Total Sales] - [LY Sales] =250,735,507.08
Sales Var % = DIVIDE([Sales Var],[LY Sales]) =5.14%
I have another value for "LY Sales", more than yours, then all 2 other figures (Sales Var & Sales Var&) also should be another.
All the figures were ok according to the lab.
Maybe you have some of filters ON?
I just encountered the same problem
My formula was:
LY Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Sales'[Date]))
I get the correct figures when I correct it to:
LY Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date]))
not sure why the difference
did exactly same as original poster and got same result... Totals fine, but %var bad.
Although I followed the instructions carefully, I had some quetions about them ... It did not make much sense to me - to use Total sales variances deriived with last years sales... seems like either the Total sales should be sliced by date, or calculated as a yearly value, then compared with last year....THen in the questions part of the lab it explicitly tells us NOT to filter or slice.
I have used DAX regularly in the past, and the instructions are simple to follow - but why is the answer wrong (like the first poster)
I Only started with the supplied powerbi code as a starting point and did not try from scratch.
I now see the problem...
Switching between the Lab instructions and PowerBI, I skipped a step.
It is the step which creates a relationship between the Date table and the Sales table.
Add the relationship - now I get the correct answer..
(However I still do not see the business sense around a Variance calc which compares last year to total sales ) .. Unless in some future lab we will refine this and either filter or use a slicer...
I've just been testing the labs with some colleagues I am training in Power BI & DAX & Power Query M and the lab works fine for us.
Reading the replies above i suggest you do the following:
1: Ensure to use the new lab 2 .pbix file as per the instructions. Unfortunately you cannot continue to use the file form Lab 1 as there are differences in the data. (See lab1 geography table is called location in Lab2)
2: @EdxStudent @ alexei7 Ensure you create the date relationship between the (Sales[date]) and (Date[date]). This is essential as the Date table provides the full list of dates where as the sales table could be missing certain dates (if sales did not happen on a day). The continuous date series in the date table is essential for the function SAMEPERIODLASTYEAR.
3: @wsnyder the business logic & DAX logic is take a single measure then do the time intelligence on that measure. The measure [Sales Var] could be as you suggest sliced by dates see the function Dateadd for example. But PBI has made our life easier providing the higher level function like SAMEPERIODLASTYEAR and TOTALYTD etc…
Guys if that at all helps please click the Like button or better mark this as your answer resolved.
Or if not continue to ask and I will try to dig deeper into the subject.
And the winner is...
Do you need help in Spanish? Check out our new Spanish community section.
Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.
Watch Microsoft Business Applications Summit sessions on-demand.