- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Query about CALCULATE function in LAB 2

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-24-2016 10:08 AM - edited 01-24-2016 10:13 AM

Hi everyone,

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 ...

Clive

## Re: Query about CALCULATE function in LAB 2

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

## Re: Query about CALCULATE function in LAB 2

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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?