Assignment 02 Instructionsx Instructions
Assignment-02-Instructions
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 1
The following tasks should be fulfilled by you:
a) Disable “Auto Date/Time” in the options to prevent automatic date formatting. You can
find the option as shown in lecture 12 (https://www.udemy.com/powerbi-complete-
introduction/learn/v4/t/lecture/7036512?start=95) => Just untick the box below “Time
intelligence”
b) Open the Query Editor and use the advanced editor to connect Power BI to the source
files. You learned how to do this in the first module
c) Go back to the data model and open the data view to create a new table. In this table,
create a calendar (the name of the formula should also be
Calendar)
for the period we
have stock data for (04. January 2010 until 11. May 2017). With that, a column named
Date
should be created automatically
Hint: Remember the calendar function we learned and use the modeling ribbon
d) Format the calendar. Only the date should be visible in the columns (no weekday)
e) With the calendar being formatted, we can turn “Auto Date/Time” back on again (see
point a) => Tick the box below “Time intelligence”
f) Create a new column named
Year
: This column should retrieve the corresponding year
out of the column
Date
in the
Calendar
table
g) Create a new column named
Quarter
: This column should retrieve the corresponding
quarter out of the column
Date
in the
Calendar
table
h) Create a new column named
Year-Quarter
: In this column, you should combine the
column
Year
and the column
Quarter
with a space in between (i.e. the result should look
like this:
2010 Qtr. 1
)
Hint: Remember the Concatenate formula we learned
i) Create a new column named
Weekday-Nr
which should retrieve the corresponding
number of the weekday of the column
Date
in the
Calendar
table
Hint: We didn't talk about that specific formula. But if you take a look at the official
documentation, you should be able to create this column: Also make sure that the
weeks are numbered as follows: 1 = Monday, 2 = Tuesday, 3 = Wednesday and so on:
https://msdn.microsoft.com/en-us/library/ee634550.aspx
j) Go to the relationship view and create a 1:1 (one to one) relationship between the
columns
Date
in the tables
Apple-Combined
and
Calendar
k) Create a *:1 (many to one) relationship between the columns Weekday-Nr in the tables
Calendar
and
Weekdays
l) Go back to the data view and open the
Apple-Combined
table. Create a calculated
column named
End-vs-Start
which calculates the % change (also formatted as %)
between the column
Price-End of day
and the column
Price-Start of day
Hint: We learned how to divide the values of two columns and how to format columns
m) Create a measure named
Measure-AveragePrice-End
, which calculates the average of
the values in the column
Price-End of day
n) Create two measures named
Measure-MinimumPrice
and
Measure-MaximumPrice
which calculate the minimum/maximum value of the column
Price-End of day
Have fun in the data model and see you in the solution video.
