01-24-2016 10:08 AM - edited 01-24-2016 10:13 AM
I have picked up something I don't understand and wonder if any of you can help me. I'm busy with the following questions in LAB 2:
"Specifically, you will create the following measures:
- Total Sales: calculates the total sales. Format this measure as Currency. (Hint: Check out the SUM function).
- LY Sales: calculates last year sales. Format this measure as Currency. (Hint: You might find the CALCULATE and SAMEPERIODLASTYEAR function useful)."
I used the calculate function to create the LY Sales measure, and got the wrong answer if I used "CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Sales'[Date]))" and the correct answer if I used "CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))"
Is it something to do with the fact that the [Date] field in the Date table has only a single occurrence of each date with no dates missing, whereas the [Date] field in the Sales table has 0 or more occurrences of each date? If so, what is the reason for this?
Thanks in advance for any assistance rendered ...
01-25-2016 10:44 AM - edited 01-25-2016 10:45 AM
You should always filter using your dimension tables. This is why it was incorrect hwn you tried to filter using the fact table. Using the date from the fact table in an expression is the same as adding a filter from the fact table. Using the date from the fact table is why the answer was incorrect as it was considered a filter part of the expression.
Filters must flow downhill. When pulling the filter out of the table, you were filtering sideways.
01-25-2016 09:52 PM - edited 01-25-2016 09:53 PM
Thank you very much for the reply. I presume you are telling me that to use the date intelligence functions in Power BI, I must always have a table of dates like the one used in the examples presented in the course I'm currently studying?