Some more possible interview questions and answers about DAX in Power BI based on my experience and understanding.
I hope, now you got some idea about DAX related interview questions after going through my previous blogs on DAX interview questions.
If you have missed that, here are the links for your reference.
In this blog, we will find some questions related to the date tables and time intelligence functions.
Please remember, only questions and answers can’t help you to clear any interview, you have to practice each and every syntax. Otherwise, it will be very difficult for you to explain any example using DAX.
Question 33: Why do we need a date table in Power BI?
In any data set which is set for a data analytics project, we can find date information.
After importing the data set, the date column is automatically recognized as a date and created a hierarchy. But sometimes we need a separate date table.
- If we want to use the Time intelligence features of Power BI, the main prerequisite is, we need one date table in the data model.
- To create a proper star-schema data model, we need different dimension tables and a date table is among them.
Question 34: What is the difference between CALENDER and CALENDERAUTO functions.
For the CALENDER() function, you need to provide the start date and end date.
Whereas, for CALENDERAUTO(), Power BI automatically finds the start year and end year from the data model where some date fields exist.
Question 35: Could you please tell me some common date-time DAX functions which you used in the project?
There are 23 date and time DAX functions.
When we create a date table, some functions are very common to use.
For example, DATE(),YEAR(), MONTH(), WEEKNUM(), WEEKDAY(), TODAY(), DATEDIFF() etc.
Question 36: How to handle multiple relationships to the Date Table?
In any data set, if we have two date fields, for example, order date and ship date, then there are two ways to handle it.
- Creating duplicate fields (calculated measures) using active and inactive relationships between the fact table and single date table.
- Instead of creating duplicate fields for each measure, we can create different date tables, for example, one for order date, another for ship date. From a maintenance point of view, it is a better solution, but it is complex to display ordered sales and shipped sales in the same report.
Question 37: If you want to use the Time Intelligence function in your project, what are the prerequisite to start with?
To start with Time Intelligence functions, we need one Date table in the data model. As well as the Date table should meet the following requirements.
- A Date column with date/time data type
- Unique values for date column
- No missing values or blanks
- The date column should cover full years
Question 38: How many types of categorizations are available in DAX time intelligence functions?
There are two main categorizations for DAX time intelligence functions, one is summarizations over time and another is comparisons over time.
Question 39: Give some examples of summarizations over time functions.
These functions are related to year-to-date, quarter-to-date, and month-to-date.
- With CALCULATE function, we can use DATESYTD, DATESQTD, DATESMTD functions.
- Without CALCULATE function, we can use simplified DAX functions TOTALYTD, TOTALQTD, and TOTALMTD.
Question 40: Give some examples of comparisons over time functions.
- SAMEPERIODLASTYEAR: It returns a set of dates shifted one year back in time.
- The DATEADD function is a generic function that uses the number and type of period to shift.
- PARALLELPERIOD: It returns the full period in the third parameter instead of the partial period like DATEADD.
Reference Material for Preparation
Here are some reference materials for your practice and preparation.
In this blog, we get some idea about more interview questions on DAX mainly date-time and time intelligence functions. We will know more DAX interview questions in the next blog.
If you have any questions related to this project, please feel free to post your comments.
Please visit my website for other technical resources.
Please like, comment and subscribe to my YouTube channel. 🙂 Keep Learning.