My study notes. Hope they help!

Contents
“This document covers the following chapters and topics for quick reference and revision.”

Chapter 1: Introduction to DAX

1. What is DAX
2. DAX vs Excel Formulas
3. Where DAX is Used (Reports vs Model)

Chapter 2: DAX Building Blocks

4. Calculated Column
5. Measure
6. Calculated Column vs Measure (Comparison)
7. When to Use Column and When to Use Measure

Chapter 3: Core DAX Concepts

8. Row Context
9. Filter Context
10. Context Transition
11. How DAX is Evaluated

Chapter 4: Basic DAX Functions

12. SUM
13. AVERAGE
14. COUNT, COUNTA, COUNTROWS
15. MIN and MAX
16. DIVIDE

Chapter 5: CALCULATE & FILTER Functions

17. CALCULATE Function (In Detail)

18. FILTER Function
19. CALCULATE vs FILTER
20. ALL, ALLEXCEPT, ALLSELECTED

Chapter 6: Date Table

21. What is Date Table

22. Why Date Table is Required
23. Creating Date Table (CALENDAR)
24. CALENDARAUTO
25. Mark as Date Table
My study notes. Hope they help!
Chapter 7: Date & Time Functions (BASICS)

26. DATE
27. YEAR
28. MONTH
29. DAY
30. TODAY
31. NOW

Chapter 8: Time Intelligence Functions (FULL DETAIL)

32. TOTALYTD
33. TOTALMTD
34. TOTALQTD
35. DATESYTD
36. DATESMTD
37. DATESQTD
38. SAMEPERIODLASTYEAR
39. DATEADD
40. PARALLELPERIOD

Chapter 9: Finance-Based DAX Calculations

41. Total Revenue

42. Total Expenses
43. Profit Calculation
44. YTD Revenue
45. YTD Expenses
46. Variance (Actual vs Budget)
47. Growth Percentage

Chapter 10: Common DAX Mistakes & Revision

48. Common DAX Mistakes

49. DAX Revision One-Liners

My study notes. Hope they help!

 Chapter 1:- Introduction to DAX

Topic 1: What is DAX?

Concept:

• DAX stands for Data Analysis Expressions.

• It is a formula language used in Power BI to create calculations.

Why we need this:

• Power BI visuals alone cannot calculate totals, ratios or time-based values.

• DAX is required to perform calculations on data after it is loaded into the model.

Explanation (Step-by-step):

• DAX is used to create:

o Measures
o Calculated Columns
• DAX works on data that is already loaded into Power BI.
• Calculations written in DAX respond dynamically to filters and slicers.
• DAX is evaluated at report level, not during data loading.

Finance Example:

• Calculating total expenses, total revenue or profit in a report requires DAX.

• Month-wise or year-wise financial analysis is done using DAX.

Common Mistakes:

• Thinking DAX is used for data cleaning (that is Power Query).

• Comparing DAX directly with Excel formulas without understanding context.

Interview Tip:

• Question: What is DAX in Power BI?

• Answer: DAX is a formula language used in Power BI to create calculations for analysis and reporting.

Topic 2: DAX vs Excel Formulas

Concept:

• DAX and Excel formulas are both used for calculations, but they work differently.
• DAX is designed for data models, while Excel formulas work on cells.

Why we need this:

• Many students try to learn DAX using Excel logic and get confused.
My study notes. Hope they help!
 • Interviewers often test this comparison to check conceptual clarity.

Explanation (Step-by-step):

• Excel formulas work on individual cells and fixed ranges.

• DAX works on entire columns and tables in the data model.
• Excel formulas calculate once per cell.
• DAX calculations respond dynamically to filters, slicers and visuals.
• DAX does not use cell references like A1, B2.

Finance Example:

• In Excel, total expenses are calculated using a SUM formula on selected cells.
• In Power BI, total expenses are calculated using a DAX measure that changes based on selected
period or department.

Common Mistakes:

• Expecting DAX to behave exactly like Excel formulas.

• Trying to use cell-based thinking while writing DAX.

Interview Tip:

• Question: Difference between DAX and Excel formulas?

• Answer: Excel formulas work on cells, while DAX works on data models and responds dynamically to
filters.

Topic 3: Where DAX is Used (Reports vs Model)

Concept:

• DAX is used to create calculations in Power BI reports and the data model.
• It is mainly used in Measures and Calculated Columns.

Why we need this:

• Students often get confused about where to write DAX and when it is applied.
• Interviewers check whether you understand the usage area of DAX.

Explanation (Step-by-step):

• DAX can be written in two places:

o Calculated Columns: Created in the data model and stored row by row.
o Measures: Created for reports and calculated at query time.
• DAX calculations depend on filters applied in reports.
• Measures are preferred for most calculations because they are dynamic and efficient.

Finance Example:

• Total Expenses and Profit are created as measures for reporting.

My study notes. Hope they help!
 • A category classification column created using logic is a calculated column.

Common Mistakes:

• Creating calculated columns when a measure is required.

• Expecting calculated columns to change with slicers.

Interview Tip:

• Question: Where is DAX used in Power BI?

• Answer: DAX is used in calculated columns and measures within the data model and reports.

Chapter 2:- DAX Building Blocks

Topic 4: Calculated Column

Concept:

• A calculated column is a column created using DAX in the data model.

• It is calculated row by row and stored in the table.

Why we need this:

• Some logic needs to be stored at row level.

• Calculated columns are useful when classification or fixed logic is required.

Explanation (Step-by-step):

• Calculated columns are created in Data View or Model View.

• They are evaluated once during data refresh.
• The result is stored in the table and does not change with slicers.
• Calculated columns increase model size because values are stored.

Finance Example:

• Creating a column to classify expenses as “Direct” or “Indirect”.

• Creating a column to tag transactions as “High Value” or “Normal” based on amount.

Common Mistakes:

• Using calculated columns for totals or aggregations.

• Expecting calculated columns to respond to filters and slicers.

Interview Tip:

• Question: What is a calculated column in DAX?

• Answer: A calculated column is a DAX-based column calculated row by row and stored in the data
model.

My study notes. Hope they help!

Topic 5: Measure

Concept:

• A measure is a DAX calculation used for aggregation and analysis.

• It is calculated at report level, not stored in the table.

Why we need this:

• Most business and financial calculations must change based on filters.

• Measures provide dynamic and flexible analysis.

Explanation (Step-by-step):

• Measures are created in Report View or Model View.

• They are calculated only when used in a visual.
• Measures respond to slicers, filters and row context in visuals.
• Measures do not increase model size significantly.

Finance Example:

• Total Revenue, Total Expenses and Profit are created as measures.

• YTD or monthly totals are always created using measures.

Common Mistakes:

• Creating measures when row-level logic is required.

• Expecting measures to behave like calculated columns.

Interview Tip:

• Question: What is a measure in Power BI?

• Answer: A measure is a DAX calculation evaluated dynamically based on filters and visuals.

Topic 6: Calculated Column vs Measure

Concept:

• Calculated Columns and Measures are both created using DAX, but they serve different purposes.
• The main difference lies in when and how they are calculated.

Why we need this:

• This is one of the most common interview questions in Power BI.

• Using the wrong one leads to performance issues and incorrect results.

Explanation (Step-by-step):

My study notes. Hope they help!

 • Calculated Column:
o Calculated row by row
o Stored in the data model
o Does not change with slicers
o Increases model size
• Measure:
o Calculated at query time
o Not stored in the table
o Changes dynamically with filters and slicers
o Preferred for aggregations
• Rule of thumb:
o Row-level logic → Calculated Column
o Aggregation and analysis → Measure

Finance Example:

• Expense classification (Direct / Indirect) → Calculated Column

• Total Expenses, Profit, YTD Revenue → Measure

Common Mistakes:

• Creating calculated columns for totals.

• Using measures for fixed row-level classification.

Interview Tip:

• Question: Difference between calculated column and measure?

• Answer: Calculated columns are stored and row-based, while measures are dynamic and evaluated
at report level.

Topic 7: When to Use Calculated Column and When to Use Measure

Concept:

• The choice between calculated column and measure depends on business requirement and
calculation behaviour.

Why we need this:

• Choosing the correct option improves performance and avoids incorrect results.
• Interviewers often test decision-making, not just definitions.

Explanation (Step-by-step):

• Use Calculated Column when:

o Logic is required at row level
o Result should be stored in the table
o Value should not change with slicers
• Use Measure when:
My study notes. Hope they help!
 o Calculation is an aggregation
o Result should change dynamically with filters
o Performance and model size matter
• General rule:
o Data preparation logic → Calculated Column
o Reporting and analysis logic → Measure

Finance Example:

• Marking transactions as “High Value” based on amount → Calculated Column

• Total Expenses by month or department → Measure

Common Mistakes:

• Using calculated columns for dynamic totals.

• Creating too many calculated columns and increasing model size.

Interview Tip:

• Question: When should you use a measure instead of a calculated column?

• Answer: A measure should be used when the calculation needs to change dynamically based on filters
and slicers.

Chapter 3:- Core DAX Concepts

Topic 8: Row Context

Concept:

• Row context means DAX evaluates a calculation one row at a time.

• It exists when DAX works on individual rows of a table.

Why we need this:

• Understanding row context is essential to understand how calculated columns work.

• Many DAX mistakes happen because row context is misunderstood.

Explanation (Step-by-step):

• Row context exists automatically in:

o Calculated Columns
o Iterator functions (like SUMX, AVERAGEX)
• In row context, DAX can access values from the current row only.
• DAX evaluates the expression separately for each row.
• Row context does not respond to slicers or report filters.

Finance Example:

• Creating a calculated column:

My study notes. Hope they help!
 o Expense Amount × Tax Rate
• For each row, DAX takes that row’s amount and tax rate and calculates the value.

Common Mistakes:

• Expecting row context to change with slicers.

• Trying to calculate totals using only row context.

Interview Tip:

• Question: What is row context in DAX?

• Answer: Row context is when DAX evaluates an expression one row at a time, mainly in calculated
columns.

Topic 9: Filter Context

Concept:

• Filter context means DAX evaluates calculations based on filters applied.

• Filters can come from visuals, slicers, rows, columns or report filters.

Why we need this:

• Measures work because of filter context.

• Without understanding filter context, DAX results look “wrong” even when the formula is correct.

Explanation (Step-by-step):

• Filter context is created by:

o Slicers
o Filters on visuals
o Rows and columns in tables or matrices
• Measures are always evaluated under filter context.
• When filters change, the measure result also changes automatically.
• Filter context works across related tables through relationships.

Finance Example:

• A Total Expenses measure shows:

o Total of all expenses normally
o Only March expenses when March is selected
o Only HR department expenses when HR is selected
• This change happens due to filter context.

Common Mistakes:

• Expecting measures to show the same value regardless of filters.

• Forgetting that relationships also apply filter context.

My study notes. Hope they help!

Interview Tip:

• Question: What is filter context in DAX?

• Answer: Filter context is the set of filters applied to data that affects how a DAX measure is evaluated.

Topic 10: Context Transition

Concept:

• Context transition is the process of converting row context into filter context.
• This happens automatically when certain DAX functions are used.

Why we need this:

• Without context transition, many calculations inside calculated columns or iterators will not work as
expected.
• Interviewers often test this concept to check deep DAX understanding.

Explanation (Step-by-step):

• Context transition occurs when:

o A measure is used inside a calculated column
o The CALCULATE function is used
• CALCULATE changes the existing row context into filter context.
• After context transition, DAX behaves like it is working with filters, not just rows.

Finance Example:

• In a calculated column, using a measure like Total Expenses triggers context transition.
• DAX converts the current row into a filter to evaluate the measure correctly.

Common Mistakes:

• Using measures inside calculated columns without understanding context transition.

• Expecting row context to automatically act like filter context.

Interview Tip:

• Question: What is context transition in DAX?

• Answer: Context transition is the conversion of row context into filter context, mainly triggered by
CALCULATE.

Topic 11: How DAX is Evaluated

Concept:

• DAX calculations are evaluated based on context.

• Context decides which data is visible to the calculation.

My study notes. Hope they help!

Why we need this:

• Many DAX formulas look correct but give wrong results due to context misunderstanding.
• Understanding evaluation logic helps in debugging DAX.

Explanation (Step-by-step):

• DAX evaluation follows this logic:

1. Identify filter context (from slicers, visuals, filters)
2. Apply relationships to propagate filters
3. Apply row context if present
4. Perform context transition if CALCULATE is used
5. Evaluate the expression
• Measures are always evaluated in filter context.
• Calculated columns are evaluated row by row.

Finance Example:

• A Total Expenses measure first checks selected month and department.

• Then it calculates the total only for the filtered data.
• Changing slicers changes the evaluation result.

Common Mistakes:

• Writing DAX without considering context.

• Assuming DAX is evaluated from left to right like Excel formulas.

Interview Tip:

• Question: How does DAX evaluation work?

• Answer: DAX evaluates expressions based on filter context, row context and context transition.

Chapter 4:- Basic DAX Functions

Topic 12: SUM Function

What it does:

• SUM is used to add all numeric values in a column.

Syntax: SUM(TableName[ColumnName])

Where to use:

• Measure
(SUM is almost always used in measures)

Finance Example:

My study notes. Hope they help!

 Total Expenses = SUM(Expenses[Amount])

• This calculates total expense amount.

• If you select a month or department, total changes accordingly.

Common Mistake:

• Using SUM on a text column.

• Writing SUM inside calculated column for totals.

Interview Tip:

• SUM works on a column, not on individual values.

Topic 13: AVERAGE Function

What it does:

• AVERAGE is used to find the average (mean) of numeric values in a column.

Syntax: AVERAGE(TableName[ColumnName])

Where to use:

• Measure

Finance Example:
Average Expense = AVERAGE(Expenses[Amount])

• This gives average expense amount.

• If month or department is selected, average changes accordingly.

Common Mistake:

• Using AVERAGE on non-numeric columns.

• Expecting AVERAGE to include blank values (blanks are ignored).

Interview Tip:

• AVERAGE = Total ÷ Number of values.

Topic 14: COUNT, COUNTA and COUNTROWS

What it does:

• These functions are used to count data in different ways.

My study notes. Hope they help!

COUNT:

Syntax: COUNT(TableName[ColumnName])

Where to use:

• Measure

Finance Example:
Number of Amount Entries = COUNT(Expenses[Amount])

• Counts only numeric values.

Common Mistake:

• Using COUNT on text columns.

COUNTA:

Syntax: COUNTA(TableName[ColumnName])

Where to use:

• Measure

Finance Example:
Number of Invoices = COUNTA (Expenses [InvoiceNumber])

• Counts all non-blank values (text, number, date).

Common Mistake:

• Assuming COUNTA counts only numbers.

COUNTROWS:

Syntax: COUNTROWS(TableName)

Where to use:

• Measure

Finance Example:
Total Transactions = COUNTROWS(Expenses)

• Counts total rows in a table.

My study notes. Hope they help!

Common Mistake:

• Using COUNT instead of COUNTROWS to count records.

Interview Tip:

• COUNT → numbers only

• COUNTA → non-blank values
• COUNTROWS → rows in table

Topic 15: MIN and MAX Functions

What it does:

• MIN is used to find the smallest value in a column.

• MAX is used to find the largest value in a column.

MIN:

Syntax: MIN(TableName[ColumnName])

Where to use:

• Measure

Finance Example:
Lowest Expense = MIN(Expenses[Amount])

This returns the minimum expense amount.

Common Mistake:

• Using MIN on text columns.

• Expecting MIN to ignore report selections.

MAX:

Syntax: MAX (TableName[ColumnName])

Where to use:

• Measure

Finance Example:
Highest Expense = MAX (Expenses [Amount])

This returns the maximum expense amount.

My study notes. Hope they help!
Common Mistake:

• Using MAX when total or average is required.

• Assuming MAX always gives the same value even after filtering.

Interview Tip:

• MIN finds the smallest value.

• MAX finds the largest value.

Topic 16: DIVIDE Function

What it does:

• DIVIDE is used to divide one value by another safely.

• It avoids errors when the denominator is zero or blank.

Syntax: DIVIDE (Numerator, Denominator)

(Optional third argument can be given if denominator is zero, but not mandatory for basics.)

Where to use:

• Measure

Finance Example:

Profit Margin = DIVIDE([Profit], [Revenue])

• This calculates profit margin.

• If Revenue is zero or blank, no error is shown.

Common Mistake:

• Using normal division ( / ) instead of DIVIDE.

• Not handling zero or blank denominator values.

Interview Tip:

• Always use DIVIDE for ratios and percentages instead of /.

Chapter 5: CALCULATE & FILTER Functions

Topic 17: CALCULATE Function

What it does:

• CALCULATE is used to change how a calculation is done.

My study notes. Hope they help!
 • It applies conditions to a calculation.

Syntax: CALCULATE (Expression, Filter1, Filter2, ...)

Where to use:

• Measure

Simple way to remember:

• First decide what to calculate

• Then decide for which data

Finance Example:
Total HR Expenses = CALCULATE (SUM (Expenses [Amount]), Expenses [Department] = "HR")

• This calculates total expenses only for HR department.

Another example:
Current Year Revenue = CALCULATE (SUM (Sales [Amount]), Sales [Year] = 2025)

Common Mistake:

• Trying to apply conditions without using CALCULATE.

• Being afraid of CALCULATE and avoiding it.

Interview Tip:

• CALCULATE is the most important function in DAX.

• It is used whenever a calculation needs conditions.

Topic 18: FILTER Function

What it does:

• FILTER is used to select specific rows from a table based on a condition.

• It checks data row by row.

Syntax: FILTER (TableName, Condition)

Where to use:

• Mostly used inside CALCULATE

• Measure

Simple way to remember:

My study notes. Hope they help!

 • FILTER means:
“From this table, keep only rows where this condition is true.”

Finance Example:
High Value Expenses = CALCULATE (SUM (Expenses [Amount]), FILTER (Expenses, Expenses [Amount]
> 10000))
• This calculates total expenses only where amount is greater than 10,000.

Another example:

Revenue for Domestic Sales=CALCULATE(SUM(Sales[Amount]),FILTER(Sales, Sales[Type] ="Domestic"))

Common Mistake:

• Using FILTER when a simple condition in CALCULATE is enough.

• Forgetting that FILTER works row by row and can affect performance.

Interview Tip:

• FILTER returns a table, not a single value.

• It is mainly used with CALCULATE.

Topic 19: CALCULATE vs FILTER

What it does:

• CALCULATE changes how a calculation is done.

• FILTER decides which rows should be used in that calculation.

Syntax:

CALCULATE: CALCULATE (Expression, Condition)

FILTER: FILTER (TableName, Condition)

Where to use:

• CALCULATE → Measure
• FILTER → Mostly inside CALCULATE

Simple way to remember:

• CALCULATE = What to calculate

• FILTER = Which data to use

Finance Example:

Using only CALCULATE (simple condition):

My study notes. Hope they help!

 HR Expenses = CALCULATE (SUM (Expenses [Amount]), Expenses [Department] = "HR")

Using CALCULATE + FILTER (row-by-row condition):

High Value Expenses = CALCULATE( SUM(Expenses[Amount]), FILTER( Expenses, Expenses[Amount]
> 10000))
When to use what:

• Simple condition → use CALCULATE only

• Condition based on row checking → use FILTER inside CALCULATE

Common Mistake:

• Using FILTER everywhere even when not required.

• Thinking CALCULATE and FILTER do the same thing.

Interview Tip:

• CALCULATE changes calculation logic.

• FILTER selects rows and returns a table.

Topic 20: ALL, ALLEXCEPT and ALLSELECTED

What it does:

• These functions control which selections are ignored or kept in a calculation.

• They are mainly used for totals, percentages and comparisons.

ALL:

What it does:

• ALL removes all selections from a table or column.

Syntax: ALL(TableName)

or

ALL(TableName[ColumnName])

Finance Example:
Total Company Expense = CALCULATE (SUM (Expenses [Amount]), ALL(Expenses))

This shows total company expense even if month or department is selected.

Common Mistake:

• Using ALL without realising it removes every filter.

My study notes. Hope they help!

ALLEXCEPT:

What it does:

• ALLEXCEPT removes all selections except the specified column.

Syntax: ALLEXCEPT (TableName, TableName[ColumnName])

Finance Example:
Department Total Expense = CALCULATE (SUM (Expenses [Amount]), ALLEXCEPT (Expenses, Expenses
[Department]))
This keeps department filter and removes all others.

Common Mistake:

• Not understanding which filters are kept and which are removed.

ALLSELECTED:

What it does:

• ALLSELECTED respects slicer selections but ignores row-level filters in visuals.

Syntax: ALLSELECTED(TableName)

Finance Example:
Expense Percentage = DIVIDE (SUM (Expenses [Amount]), CALCULATE (SUM (Expenses [Amount]),
ALLSELECTED(Expenses)))
This calculates percentage within selected data.

Common Mistake:

• Confusing ALLSELECTED with ALL.

Interview Tip:

• ALL ignores everything

• ALLEXCEPT ignores everything except specified column
• ALLSELECTED respects slicers

Chapter 6: Date Table

Topic 21: What is a Date Table

What it does:

My study notes. Hope they help!

 • A Date Table is a table that contains all dates in a continuous range.
• It is used for time-based calculations in Power BI.

Why we need this:

• Calculations like monthly, yearly and YTD work properly only with a Date Table.
• Power BI cannot understand time correctly without it.

Simple understanding:

• Transaction tables may not have all dates.

• Date Table fills missing dates and creates a proper timeline.
• Other tables are connected to the Date Table using the Date column.

What a Date Table usually contains:

• Date
• Year
• Month
• Month Name
• Quarter

Finance Example:

• To analyse monthly expenses or yearly revenue trends, a Date Table is required.

• Without it, time-based reports give wrong results.

Common Mistake:

• Using transaction date directly without creating a Date Table.

• Creating multiple Date Tables unnecessarily.

Interview Tip:

• Date Table is mandatory for time intelligence functions in Power BI.

Topic 22: Why Date Table is Required

What it does:

• A Date Table helps Power BI understand time properly.

• It allows correct calculation of monthly, yearly and cumulative values.

Why we need this:

• Financial analysis always depends on time.

• Time-based DAX functions work correctly only when a Date Table is present.

Simple understanding:
My study notes. Hope they help!
 • Transaction data may miss some dates.
• Date Table contains all dates without gaps.
• Power BI uses this table to compare months, years and periods accurately.
• Date Table acts as a single reference for all date calculations.

Finance Example:

• Monthly expense comparison requires a Date Table.

• Year-to-date revenue calculation needs a Date Table to work correctly.

What happens without Date Table:

• YTD, MTD and previous year calculations give wrong results.

• Comparisons between months or years become inaccurate.

Common Mistake:

• Thinking a single date column is enough.

• Relying on Power BI auto date feature instead of a proper Date Table.

Interview Tip:

• Date Table is mandatory for all time intelligence functions

Topic 23: Creating Date Table using CALENDAR

What it does:

• CALENDAR is used to create a Date Table in Power BI.

• It generates dates between a start date and an end date.

Why we need this:

• To create our own proper Date Table instead of relying on auto dates.
• Required for time-based calculations like YTD, MTD, yearly comparison.

Syntax: CALENDAR (StartDate, EndDate)

Where to use:

• Calculated Table
(Create from Modeling → New Table)

Simple Understanding:

• CALENDAR creates one row for each date between two given dates.
• You decide the start and end date.

Basic Example:
DateTable = CALENDAR (DATE (2020, 1, 1), DATE (2025, 12, 31)) My study notes. Hope they help!
 • This creates a Date Table from 1 Jan 2020 to 31 Dec 2025.

Adding useful columns (example):

Year = YEAR(DateTable[Date])
Month = MONTH(DateTable[Date])

Finance Example:

• Expense and sales tables are connected to this Date Table.

• Monthly, yearly and YTD analysis becomes accurate.

Common Mistake:

• Creating Date Table but not connecting it to fact tables.

• Creating Date Table with limited date range.

Interview Tip:

• CALENDAR is used to manually create a Date Table with a fixed date range.

Topic 24: CALENDARAUTO Function

What it does:

• CALENDARAUTO is used to automatically create a Date Table.

• It automatically detects the date range from your data.

Why we need this:

• When you don’t want to manually define start and end dates.
• Useful when date range keeps changing as new data is added.

Syntax: CALENDARAUTO()

Where to use:

• Calculated Table
(Create from Modeling → New Table)

Simple Understanding:

• CALENDARAUTO scans all date columns in the model.

• It finds the earliest and latest date.
• Then it creates a Date Table covering that full range.

Basic Example:
DateTable = CALENDARAUTO() My study notes. Hope they help!
 • Power BI automatically creates the date range.

Finance Example:

• If sales data keeps increasing every month, CALENDARAUTO adjusts automatically.

• No need to change date range manually.

Common Mistake:

• Using CALENDARAUTO without understanding which date columns exist in the model.
• Forgetting to mark the table as Date Table.

Interview Tip:

• CALENDARAUTO automatically creates Date Table based on model dates.

Topic 25: Mark as Date Table

What it does:

• Mark as Date Table tells Power BI that a table is the official Date Table.
• It enables time-based DAX functions to work correctly.

Why we need this:

• Power BI does not automatically know which table is the Date Table.
• Time intelligence functions work properly only after marking the Date Table.

Where to do this:

• Power BI Desktop
• Go to Modeling → Mark as Date Table

Simple Understanding:

• You select the Date Table.

• You choose the column that contains unique dates.
• Power BI then uses this table for all time calculations.

Steps (Easy to remember):

1. Select the Date Table

2. Click Modeling tab
3. Click Mark as Date Table
4. Choose the Date column
5. Click OK

My study notes. Hope they help!

Finance Example:

• After marking the Date Table, calculations like YTD expenses and yearly revenue comparison work
correctly.
• Without this step, results may be incorrect.

Common Mistake:

• Creating a Date Table but forgetting to mark it.

• Marking a table that contains duplicate dates.

Interview Tip:

• Marking Date Table is mandatory for time intelligence functions.

Chapter 7: Date & Time Functions (BASICS)

Topic 26: DATE Function

What it does:

• DATE is used to create a date value using year, month and day.

Syntax: DATE (Year, Month, Day)

Where to use:

• Calculated Column
• Measure
• Calculated Table

Simple Understanding:

• DATE combines year, month and day into a proper date.

• Useful when date values are stored separately.

Finance Example:
Transaction Date = DATE (Transactions [Year], Transactions [Month], Transactions [Day])

• This creates a full date from separate year, month and day columns.

Another example:
Start Date = DATE (2025, 1, 1)

Common Mistake:

• Passing text values instead of numbers.

My study notes. Hope they help!
 • Using incorrect month or day values.

Interview Tip:

• DATE is used to construct a valid date from year, month and day values.

Topic 27: YEAR Function

What it does:

• YEAR is used to extract the year from a date.

Syntax: YEAR(DateColumn)

Where to use:

• Calculated Column
• Measure

Simple Understanding:

• YEAR takes a date and returns only the year part.

• Useful for grouping or filtering data by year.

Finance Example:
Transaction Year = YEAR (Expenses [TransactionDate])

• This creates a column showing the year of each transaction.

Another example (measure use):

Selected Year = YEAR(MAX(DateTable[Date]))

Common Mistake:

• Using YEAR on text instead of a date column.

• Forgetting to use a proper Date Table.

Interview Tip:

• YEAR is used to extract the year value from a date.

Topic 28: MONTH Function

What it does:

• MONTH is used to extract the month number from a date.

My study notes. Hope they help!
 • Month number ranges from 1 (January) to 12 (December).

Syntax: MONTH(DateColumn)

Where to use:

• Calculated Column
• Measure

Simple Understanding:

• MONTH takes a date and returns only the month part as a number.
• Useful for monthly analysis and grouping.

Finance Example:
Transaction Month = MONTH(Expenses[TransactionDate])

• This creates a column showing month number for each transaction.

Another example (measure use):

Selected Month = MONTH(MAX(DateTable[Date]))

Common Mistake:

• Expecting MONTH to return month name (it returns number only).

• Using MONTH on text instead of date column.

Interview Tip

• MONTH returns a numeric month value from a date.

Topic 29: DAY Function

What it does:

• DAY is used to extract the day number from a date.

• Day number ranges from 1 to 31.

Syntax: DAY(DateColumn)

Where to use:

• Calculated Column
• Measure

Simple Understanding:

My study notes. Hope they help!

 • DAY takes a date and returns only the day part.
• Useful when day-wise analysis or logic is required.

Finance Example: Transaction Day = DAY(Expenses[TransactionDate])

This creates a column showing the day of the month for each transaction.

Another example (measure use):

Selected Day = DAY(MAX(DateTable[Date]))

Common Mistake:

• Using DAY on text instead of a date column.

• Expecting DAY to return weekday name (it returns day number only).

Interview Tip:

• DAY returns the day number from a date.

Topic 30: TODAY Function

What it does

• TODAY returns the current date (today’s date).

Syntax: TODAY()

Where to use:

• Measure
• Calculated Column
• Calculated Table

Simple Understanding:

• TODAY always shows the current system date.

• The value updates automatically when the report is refreshed.

Finance Example:
Today Date = TODAY()

Another practical example:

Days Difference = DATEDIFF (Expenses [TransactionDate], TODAY(), DAY)

My study notes. Hope they help!
 • This can be used to calculate how many days have passed since a transaction.

Common Mistake:

• Expecting TODAY to change automatically without refreshing the report.

• Using TODAY when report needs a fixed date (like closing date).

Interview Tip:

• TODAY returns only the date, not time.

Topic 31: NOW Function

What it does:

• NOW returns the current date and current time.

Syntax: NOW()

Where to use:

• Measure
• Calculated Column
• Calculated Table

Simple Understanding:

• NOW shows both date + time together.

• The value updates when the report is refreshed.
• It is more detailed than TODAY.

Finance Example:
Last Refresh Time = NOW()

• Used to show when the report was last refreshed.

Another example:
Hours Since Transaction = DATEDIFF (Expenses [TransactionDateTime], NOW(), HOUR)

Common Mistake:

• Using NOW when only date is required (TODAY is enough).

• Expecting NOW to update continuously without refreshing.

Interview Tip:

• TODAY → date only

My study notes. Hope they help!
 • NOW → date and time

Chapter 8: Time Intelligence Functions (FULL DETAIL)

Topic 32: TOTALYTD Function

What it does:

• TOTALYTD calculates the Year-to-Date total.

• It adds values from the start of the year till the selected date.

Syntax: TOTALYTD (Expression, DateColumn)

Where to use:

• Measure

Important requirement:

• A proper Date Table must exist

• Date Table must be marked as Date Table

Simple Understanding:

• TOTALYTD answers:
“From January till today (or selected month), what is the total?”

Finance Example:
YTD Expenses = TOTALYTD (SUM (Expenses [Amount]), DateTable[Date])

• If March is selected, it shows total expenses from Jan to March.

Common Mistake:

• Using TOTALYTD without a Date Table.

• Using transaction date instead of Date Table date.

Interview Tip:

• TOTALYTD is used for cumulative yearly totals.

Topic 33: TOTALMTD Function

What it does:

• TOTALMTD calculates the Month-to-Date total.

• It adds values from the start of the month till the selected date.
My study notes. Hope they help!
TOTALMTD (Expression, DateColumn)
Syntax:

Where to use:

• Measure

Important requirement:

• A proper Date Table must exist

• Date Table must be marked as Date Table

Simple Understanding:

• TOTALMTD answers:
“From the first day of this month till today (or selected date), what is the total?”

Finance Example:
MTD Expenses = TOTALMTD (SUM (Expenses [Amount]), DateTable[Date])

• If 15th April is selected, it shows total expenses from 1st April to 15th April.

Common Mistake:

• Using TOTALMTD without a Date Table.

• Expecting TOTALMTD to work across multiple months.

Interview Tip:

• TOTALMTD is used for month-wise cumulative totals.

Topic 34: TOTALQTD Function

What it does:

• TOTALQTD calculates the Quarter-to-Date total.

• It adds values from the start of the quarter till the selected date.

Syntax: TOTALQTD (Expression, DateColumn)

Where to use:

• Measure

Important requirement:

• A proper Date Table must exist

• Date Table must be marked as Date Table

My study notes. Hope they help!

Simple Understanding:

• TOTALQTD answers:
“From the beginning of this quarter till today (or selected date), what is the total?”

Finance Example:
QTD Expenses = TOTALQTD (SUM (Expenses [Amount]),DateTable[Date])

• If May is selected, it shows total expenses from start of the quarter (April) to May.

Common Mistake:

• Using TOTALQTD without a Date Table.

• Expecting TOTALQTD to calculate across different quarters.

Interview Tip:

• TOTALQTD is used for quarter-wise cumulative analysis.

Topic 35: DATESYTD Function

What it does:

• DATESYTD returns a set of dates from the start of the year till the selected date.
• It is mainly used inside CALCULATE.

Syntax: DATESYTD(DateColumn)

Where to use:

• Inside CALCULATE
• Measure

Simple Understanding:

• DATESYTD does not calculate values by itself.

• It only tells Power BI which dates to consider (Jan to selected date).
• CALCULATE then performs the actual calculation.

Finance Example:
YTD Expenses = CALCULATE (SUM (Expenses [Amount]),DATESYTD (DateTable[Date]))

• If March is selected, it calculates expenses from January to March.

Difference from TOTALYTD (important):

• TOTALYTD = shortcut function (calculation + dates together)

My study notes. Hope they help!

 • DATESYTD = only date filter, used with CALCULATE

Common Mistake:

• Using DATESYTD without CALCULATE.

• Using transaction date instead of Date Table date.

Interview Tip:

• DATESYTD returns a table of dates, not a number.

Topic 36: DATESMTD Function

What it does:

• DATESMTD returns a set of dates from the start of the month till the selected date.
• It is mainly used inside CALCULATE.

Syntax: DATESMTD(DateColumn)

Where to use:

• Inside CALCULATE
• Measure

Simple Understanding:

• DATESMTD does not calculate totals by itself.

• It only defines which dates to include (start of month to selected date).
• CALCULATE then performs the calculation.

Finance Example:

MTD Expenses = CALCULATE (SUM (Expenses [Amount]), DATESMTD(DateTable[Date]))

• If 15th April is selected, it calculates expenses from 1st April to 15th April.

Difference from TOTALMTD (important):

• TOTALMTD = shortcut function

• DATESMTD = date filter used with CALCULATE

Common Mistake:

• Using DATESMTD without CALCULATE.

• Using transaction date instead of Date Table date.

Interview Tip:

My study notes. Hope they help!

 • DATESMTD returns a table of dates, not a value.

Topic 37: DATESQTD Function

What it does:

• DATESQTD returns a set of dates from the start of the quarter till the selected date.
• It is mainly used inside CALCULATE.

Syntax: DATESQTD(DateColumn)

Where to use:

• Inside CALCULATE
• Measure

Simple Understanding:

• DATESQTD does not calculate totals by itself.

• It only defines which dates to include (start of quarter to selected date).
• CALCULATE then performs the calculation.

Finance Example:
QTD Expenses = CALCULATE (SUM (Expenses [Amount]),DATESQTD (DateTable[Date]))

• If May is selected, it calculates expenses from start of the quarter (April) to May.

Difference from TOTALQTD (important):

• TOTALQTD = shortcut function

• DATESQTD = date filter used with CALCULATE

Common Mistake:

• Using DATESQTD without CALCULATE.

• Using transaction date instead of Date Table date.

Interview Tip:

• DATESQTD returns a table of dates, not a number.

Topic 38: SAMEPERIODLASTYEAR Function

What it does:

• SAMEPERIODLASTYEAR returns the same dates of the previous year.

• It is used for year-on-year comparison.

My study notes. Hope they help!

Syntax: SAMEPERIODLASTYEAR(DateColumn)

Where to use:

• Inside CALCULATE
• Measure

Simple Understanding:

• If current selection is March 2025, this function returns March 2024 dates.
• It does not calculate values itself.
• CALCULATE performs the actual calculation.

Finance Example:
Last Year Expenses = CALCULATE(SUM(Expenses [Amount]),SAMEPERIODLASTYEAR(DateTable[Date]))

• This calculates expenses for the same period last year.

Very common use:

YOY Growth = DIVIDE ([Total Expenses] - [Last Year Expenses], [Last Year Expenses])

Important Requirement:

• Proper Date Table

• Date Table must be marked as Date Table

Common Mistake:

• Using SAMEPERIODLASTYEAR without a Date Table.

• Using transaction date instead of Date Table date.

Interview Tip:

• SAMEPERIODLASTYEAR is used for year-on-year comparison.

Topic 39: DATEADD Function

What it does:

• DATEADD shifts dates forward or backward by a given number of days, months or years.
• It is used to compare current period with previous or next period.

Syntax: DATEADD (DateColumn, Number, Interval)

Interval values:

• DAY
My study notes. Hope they help!
 • MONTH
• YEAR

Where to use:

• Inside CALCULATE
• Measure

Simple Understanding:

• DATEADD tells Power BI:

“Take these dates and move them back or forward by some time.”

Finance Example (Previous Year):

Previous Year Expenses = CALCULATE (SUM(Expenses [Amount]), DATEADD(DateTable[Date], -1,
YEAR))
Finance Example (Previous Month):
Previous Month Expenses = CALCULATE (SUM(Expenses [Amount]),DATEADD(DateTable[Date], -1,
MONTH))
Important Requirement:

• Proper Date Table

• Date Table must be marked as Date Table

Common Mistake:

• Using DATEADD without a Date Table.

• Using positive value when negative is required for previous period.

Interview Tip:

• DATEADD is more flexible than SAMEPERIODLASTYEAR.

Topic 40: PARALLELPERIOD Function

What it does:

• PARALLELPERIOD returns dates from a parallel period in the past or future.

• It is mainly used for period comparison like previous year or previous quarter.

Syntax: PARALLELPERIOD (DateColumn, Number, Interval)

Interval values:

• YEAR
• QUARTER
• MONTH
My study notes. Hope they help!
Where to use:

• Inside CALCULATE
• Measure

Simple Understanding:

• PARALLELPERIOD shifts the entire period, not individual dates.

• It works at month, quarter or year level.

Finance Example (Previous Year):

Previous Year Expense = CALCULATE (SUM (Expenses [Amount]),PARALLELPERIOD (DateTable[Date], -
1, YEAR))

Finance Example (Previous Quarter):

Previous Quarter Expense = CALCULATE (SUM (Expenses [Amount]), PARALLELPERIOD

(DateTable[Date], -1, QUARTER))
I

Important Requirement:

• Proper Date Table

• Date Table must be marked as Date Table

Common Mistake:

• Using PARALLELPERIOD for day-level comparison (it does not work well for days).
• Confusing PARALLELPERIOD with DATEADD.

Interview Tip:

• PARALLELPERIOD works best for month, quarter and year comparisons.

Chapter 9: Finance-Based DAX Calculations

Topic 41: Total Revenue Calculation

What it does:

• Calculates the total revenue amount from the revenue column.

• This is the base measure for most financial analysis.

Syntax: SUM (TableName [RevenueColumn])

Where to use:
My study notes. Hope they help!
 • Measure

Simple Understanding:

• Total Revenue adds all revenue values.

• The value changes automatically when you select:
o Month
o Year
o Product
o Region

Finance Example:
Total Revenue = SUM (Sales [Revenue])

• Shows total revenue based on current report selection.

Common Mistake:

• Creating Total Revenue as a calculated column instead of a measure.

• Using wrong column (gross amount vs net amount).

Interview Tip:

• Total Revenue should always be a measure, not a calculated column.

Topic 42: Total Expenses Calculation

What it does:

• Calculates the total expense amount from the expense column.

• This is a basic and most frequently used financial measure.

Syntax: SUM (TableName [ExpenseColumn])

Where to use:

• Measure

Simple Understanding:

• Total Expenses adds all expense values.

• The value changes automatically based on selections like:
o Month
o Department
o Cost center

Finance Example:

My study notes. Hope they help!

 Total Expenses = SUM (Expenses [Amount])
• Shows total expenses as per current report selection.

Common Mistake:

• Creating Total Expenses as a calculated column.

• Including non-expense values (like taxes or adjustments) without checking.

Interview Tip:

• Total Expenses is always created as a measure and reacts to filters.

Topic 43: Profit Calculation

What it does:

• Calculates profit by subtracting total expenses from total revenue.

Syntax: [Total Revenue] - [Total Expenses]

Where to use:

• Measure

Simple Understanding:

• Profit shows how much is earned after deducting expenses.

• The value changes automatically based on selections like month, year or department.

Finance Example:
Profit = [Total Revenue] - [Total Expenses]

• Uses already created measures for revenue and expenses.

• This is the correct and recommended approach.

Common Mistake:

• Writing SUM(Revenue) - SUM(Expenses) again instead of using existing measures.

• Creating profit as a calculated column.

Interview Tip:

• Profit should be calculated using measures, not columns.

• Reusing measures improves performance and clarity.

Topic 44: YTD Revenue Calculation

My study notes. Hope they help!

What it does:

• Calculates Year-to-Date Revenue.

• Adds revenue from the start of the year till the selected date.

Syntax: TOTALYTD (Expression, DateColumn)

Where to use:

• Measure

Important requirement:

• Proper Date Table

• Date Table must be marked as Date Table

Simple Understanding:

• YTD Revenue answers:

“From January till now (or selected month), how much revenue is generated?”

Finance Example:
YTD Revenue = TOTALYTD ([Total Revenue],DateTable [Date])

• If June is selected, it shows revenue from January to June.

Common Mistake:

• Using transaction date instead of Date Table date.

• Calculating YTD without marking Date Table.

Interview Tip:

• YTD measures are always created using Date Table, not transaction dates.

Topic 45: YTD Expenses Calculation

What it does:

• Calculates Year-to-Date Expenses.

• Adds expenses from the start of the year till the selected date.

Syntax: TOTALYTD (Expression, DateColumn)

Where to use:

• Measure

My study notes. Hope they help!

Important requirement:

• Proper Date Table

• Date Table must be marked as Date Table

Simple Understanding:

• YTD Expenses answers:

“From January till now (or selected month), how much expense has occurred?”

Finance Example:

YTD Expenses = TOTALYTD ([Total Expenses], DateTable [Date])

• If August is selected, it shows expenses from January to August.

Common Mistake:

• Using TOTALYTD on a raw column instead of an existing measure.

• Forgetting to connect fact table with Date Table.

Interview Tip:

• YTD Expenses is always created as a measure and depends on Date Table.

Topic 46: Variance Calculation (Actual vs Budget)

What it does:

• Calculates the difference between Actual value and Budget value.

• Helps analyse over-performance or under-performance.

Syntax: [Actual Value] - [Budget Value]

Where to use:

• Measure

Simple Understanding:

• Variance shows how much actual result differs from plan.

• Positive variance → favourable (depends on context)
• Negative variance → unfavourable

Finance Example:

First create base measures:

My study notes. Hope they help!

 Actual Amount = SUM (Actuals [Amount])
Budget Amount = SUM (Budget [Amount])

Then variance:
Variance = [Actual Amount] - [Budget Amount]

• Used for expense variance, revenue variance, cost control analysis.

Common Mistake:

• Mixing actual and budget from different date or category levels.

• Writing SUM formulas again instead of using existing measures.

Interview Tip:

• Variance is always calculated using measures, not columns.

• Base measures should be created first (Actual, Budget).

Topic 47: Growth Percentage Calculation

What it does:

• Calculates growth or change in percentage between two values.

• Commonly used for year-on-year or period comparison.

Syntax: DIVIDE (Current Value - Previous Value, Previous Value)

Where to use:

• Measure

Simple Understanding:

• Growth % shows how much increase or decrease happened compared to the previous period.
• Positive value → growth
• Negative value → decline

Finance Example:

Assume these measures already exist:

Current Revenue = [Total Revenue]

Last Year Revenue = [Last Year Revenue]

Growth percentage:

Revenue Growth % = DIVIDE ([Current Revenue] - [Last Year Revenue], [Last Year Revenue])

My study notes. Hope they help!

 • This shows revenue growth compared to last year.

Another example (Expense growth):

Expense Growth % = DIVIDE ([YTD Expenses] - [Last Year Expenses], [Last Year Expenses])

Why DIVIDE is used:

• Prevents error when previous value is zero or blank.

Common Mistake:
• Using normal division instead of DIVIDE.
• Forgetting to compare same period (e.g. current year vs last year).

Interview Tip:

• Growth % = (Current − Previous) ÷ Previous

• Always use DIVIDE for percentage calculations.

Chapter 10: Common DAX Mistakes & Revision

Topic 48: Common DAX Mistakes

What it is:

• This section lists most common mistakes students and beginners make while writing DAX.
• Very important for interviews and debugging.

Mistake 1: Using Calculated Column instead of Measure

• Totals, YTD, ratios should be measures, not columns.

• Columns are calculated row by row and increase model size.

Mistake 2: Writing SUM again and again instead of using measures

• Repeating SUM formulas makes code messy.

• Always reuse existing measures like [Total Revenue].

Mistake 3: Not using a Date Table

• Time intelligence functions fail or give wrong results.

• Always create and mark a proper Date Table.

Mistake 4: Using transaction date instead of Date Table date

• Date functions must use DateTable [Date].

• Using fact table date breaks YTD and comparison logic.

My study notes. Hope they help!

Mistake 5: Using “/” instead of DIVIDE

• Normal division causes errors when denominator is zero.

• Always use DIVIDE for ratios and percentages.

Mistake 6: Ignoring filters and selections

• Measures always change based on report selections.

• Expecting fixed values leads to confusion.

Mistake 7: Overusing FILTER

• FILTER should be used only when row-by-row condition is needed.

• Simple conditions should be written directly in CALCULATE.

Interview Tip:

• Interviewers often ask:

“Why is this DAX giving wrong result?”
• Most answers come from these common mistakes.

Topic 49: DAX Revision One-Liners

DAX Basics:

• DAX is used for calculations, not data cleaning.

• Power Query → data preparation, DAX → analysis.

Calculated Column vs Measure:

• Calculated Column → row by row, stored in table.

• Measure → calculated at report level, dynamic.
• Totals, ratios, YTD → always measures.

Context:

• Row context → works row by row.

• Filters and slicers decide which data is calculated.
• CALCULATE changes how a calculation behaves.

Core Functions:

• SUM → adds values of a column.

• AVERAGE → finds mean value.
• COUNT → counts numbers only.
• COUNTROWS → counts rows in a table.
• DIVIDE → safe division, avoids errors.

CALCULATE & FILTER:

My study notes. Hope they help!
 • CALCULATE → applies conditions to a calculation.
• FILTER → selects rows based on condition.
• Simple condition → CALCULATE only.
• Row-by-row logic → CALCULATE + FILTER.

ALL Functions:

• ALL → removes all selections.

• ALLEXCEPT → keeps one filter, removes others.
• ALLSELECTED → respects slicers.

Date Table:

• Date Table is mandatory for time intelligence.

• Always use DateTable[Date] in date functions.
• Mark Date Table before using YTD, MTD, etc.

Time Intelligence:

• TOTALYTD → year-to-date total.

• TOTALMTD → month-to-date total.
• SAMEPERIODLASTYEAR → last year comparison.
• DATEADD → shift dates forward or backward.

Finance Calculations:

• Profit = Revenue − Expenses.

• Variance = Actual − Budget.
• Growth % = (Current − Previous) ÷ Previous.

Golden Rule:

• If result should change with filters → use a measure.

My study notes. Hope they help!