DAX

Formulas
in Power
BI
SUM
FORMUL
A
 SUM Formula in Power
BI
Definition: SUM is a simple aggregation function that adds
up all the values in a single column.

Example
Total_Sales = SUM(SalesData[Sales])

Use Case: Use SUM when you want to sum up all the
values in a numeric column without any complex row-
by-row operations.

Performance: Since it operates directly on a column,

it's fast and efficient for simple summations.
 Calculated Column in Power BI
Definition: A calculated column is a new column that you
create in a table using DAX (Data Analysis Expressions). The
value for each row is calculated when the column is created,
and it remains static unless the data is refreshed.

Example
Tax = SalesData[Total_Sales]*18/100

Use Cases: Suitable for scenarios where you need a value

for each row in the table, such as adding a new field derived
from existing data (e.g., creating a "Total Price" column by
multiplying "Quantity" and "Price").
Performance: Since calculated columns are stored in the
Calculated Column in Power BI
Calculated Column in Power BI
DIFFERENCE BETWEEN CALCULATED COLUMN AND MEASURE IN
POWER BI

In Power BI, both calculated columns and measures are used to perform
calculations, but they serve different purposes and behave differently. Here's a
comparison of the two:
SUMX
FORMUL
A
 SUMX Formula in Power
BI
Definition: SUMX is an iterator function that performs
row-by-row calculations and then sums the results.

Example
Total_Sales2 = SUMX(SalesData,SalesData[Quantity]*SalesData[Unit
Price (INR)])

Use Case: Use SUMX when you need to perform a

calculation for each row before summing, such as multiplying
two columns together or applying conditional logic.
Performance: SUMX can be slower than SUM because it
performs calculations on each row individually before
summing. It's ideal for more complex scenarios that require
Difference between SUM and SUMX Formula
in Power BI
In Power BI, both SUM and SUMX are used to perform summation, but they
work differently
and are used in different scenarios. Here's a breakdown of the key differences:
COUNT
FORMUL
A
 Count Formula in Power
BI
Definition: COUNT counts the number of non-blank values in
a column.

Example
Number of Customers = COUNT('Table'[Customer ID])

Use Case: When you want to count the non-empty values in

a column.
COUNTA
FORMUL
A
 COUNTA Formula in Power
BI
Definition: COUNTA counts the number of non-blank values
in a column, including text, numeric values, and logical
values even counts Boolean values where only Count will not
count Boolean data type
Example
Number of Reviews = COUNTA('Table'[Review Status])

Use Case: When you need to count all non-blank values,

regardless of the
data type (text, numbers, etc.).
COUNTBLA
NK
FORMULA
 COUNTBLANK Formula in Power BI

Definition: COUNTBLANK counts the number of blank (empty)

values in a column.

Example
Blank Reveiws = COUNTBLANK('Table'[Review Points])

Use Case: When you want to count the number of blank or

missing values
in a column.
COUNTRO
WS
FORMULA
 COUNTROWS Formula in Power BI

Definition: COUNTROWS counts the number of rows

in a table.

Example
Total Records = COUNTROWS('Table')

Use Case: When you want to count the total number of rows in
a table or in
a filtered table.
DISTINCTCOU
NT FORMULA
 DISTINCTCOUNT Formula

Definition: DISTINCTCOUNT counts the number of unique,

non-blank values in a column.

Example
Unique States = DISTINCTCOUNT('Table'[State])

Use Case: When you need to count the distinct (unique)

values in a
column, excluding blanks.
COUNTX
FORMUL
A
 COUNTX Formula

Definition: COUNTX is an iterator function that counts non-

blank results of an expression evaluated row by row over a
table.

Example
Count Reviews >= 5 = COUNTX('Table', IF('Table'[Review Points] >= 5, 1,
BLANK()))

Use Case: When you need to count based on an

expression that is
evaluated for each row in a table.
COUNTA
X
FORMUL
A
 COUNTAX Formula

Definition: COUNTAX is the iterator version of COUNTA. It

evaluates an expression for each row and counts the
number of non-blank results.

Example
Count True = COUNTAX(FILTER('Table','Table'[Review
Status]=true),'Table'[Review Status])

Use Case: When you need to count based on an

expression that is
evaluated for each row in a table even there is Binary
data type.
COUNTRO
WS
with
FILTER
FORMUL
A
 COUNTROWS with FILTER Formula

Definition: COUNTROWS can be used with the FILTER function

to count rows that meet specific criteria.

Example
Maharashtra Count = COUNTROWS(FILTER('Table','Table'[State]="Maharashtra"))

Use Case: When you want to count rows based on a

condition or set of
conditions.
Summary of DAX Counting Functions:
These functions provide flexibility depending on whether you're counting all
rows, distinct
values, non-blank values, or values based on expressions and conditions.
Date
Formulas
Extract
Day/Month/Ye
ar
 Extract Day/Month/Year

DAY(<datetime>) : Extracts the day from a date

value.

MONTH(<datetime>) : Extracts the month from a

date value. YEAR(<datetime>) : Extracts the year

from a date value.

 Extract Hour/Minute/Second

Hour(<datetime>) : Extracts the Hour from a date and time

value.

Minute(<datetime>) : Extracts the Minute from a date and

time value. Second(<datetime>) : Extracts the Second from

a date and time value.

 Extract Hour/Minute/Second

Today() : Show Current date

Now() – Show current date and Time

Weekday() – Show Weekday in numbers

between 1 to 7 Weeknum() – Show Week

number in Month/Year
 DatedIFF
Formula
Definition: Returns the difference between two dates in the
specified interval (days, months, years, etc.).

Syntax
DATEDIFF(<start_date>, <end_date>, <interval>)

Example
DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY)
Create
Custom
Calendar
1 . Create Custom Calendar in Power
Query
Formula
= [Link](#date(2023,1,1),731,#duration(1,0,0,0))

Creating a custom calendar using Power Query in Power BI is

a common and essential task when dealing with date-related
data, especially for performing time-based analysis like year-over-
year (YoY), month-to-date (MTD), and quarter-to-date (QTD)
comparisons. Having a custom date table ensures that you have
control over the format, date ranges, and any specific time-based
logic needed for your reports.
2. Create Custom Calendar in Power
Query
M Code
let
// Define start and end dates for the calendar
StartDate = #date(2020, 1, 1), // You can change this to your desired start date
EndDate = [Link]([Link]()), // Automatically takes today's date as the end
date
// Generate a list of dates between the start and end dates
DateList = [Link](StartDate, [Link](EndDate - StartDate) + 1, #duration(1, 0, 0,
0)),
// Convert the list into a table
DateTable = [Link](DateList, [Link](), {"Date"}, null,
[Link]),
// Add Year, Month, and Day columns for further analysis
AddYear = [Link](DateTable, "Year", each [Link]([Date]),
[Link]), AddMonth = [Link](AddYear, "Month", each
[Link]([Date]), [Link]), AddDay = [Link](AddMonth, "Day",
each [Link]([Date]), [Link]),
AddMonthName = [Link](AddDay, "Month Name", each
[Link]([Date], "MMMM"), type text),
AddQuarter = [Link](AddMonthName, "Quarter", each
[Link]([Date]), [Link]),
AddYearMonth = [Link](AddQuarter, "Year-Month", each [Link]([Date], "yyyy-
MM"), type text),
AddWeekOfYear = [Link](AddYearMonth, "Week of Year", each
[Link]([Date]), [Link]),
AddDayOfWeek = [Link](AddWeekOfYear, "Day of Week", each [Link]([Date],
"dddd"), type text),
 3 . Calendar DAX Formula

Formula
= CALENDAR(MIN(SalesData[Date]),MAX(SalesData[Date]))

The CALENDAR DAX function in Power BI is used to generate a

continuous range of dates between a specified start and end
date. It's particularly useful when building a date table, which is
essential for time-based calculations such as year-over-year
analysis, month-to-date, quarter-to- date, etc.
 4 . CalendarAuto DAX Formula

Formula
= CALENDARAUTO()

The CALENDARAUTO function in Power BI is a powerful tool for

creating a date table that automatically detects the date range
from all date columns in your data model. This means it scans
your data and generates a date range based on the minimum
and maximum dates found in the model, which is especially
helpful for dynamic date tables without specifying start or end
dates manually.
 MTD QTD AND YTD DAX
FORMULA
Formu
la
Sales MTD = TOTALMTD(SUM(Sales[SalesAmount] DateTable[Date]
), )
Sales QTD = TOTALQTD(SUM(Sales[SalesAmount] DateTable[Date]
), )
Sales YTD = TOTALYTD(SUM(Sales[SalesAmount] DateTable[Date]
), )

MTD (Month-to-Date), QTD (Quarter-to-Date), and YTD (Year-to-

Date) are commonly used DAX functions in Power BI for
performing time-based aggregations. They are particularly
helpful for tracking progress over the current month, quarter, or
year, making it easy to see cumulative totals up to the present
date. These functions rely on having a properly structured date
table, ideally linked to your data model.
 Networkdays DAX Formula

Formu
la
Network Days = NETWORKDAYS("01-01-2023","31-01-
2023",1,Holidays)

In Power BI, Network Days refers to the count of working days

(typically Monday through Friday) between two specified dates,
excluding weekends and optionally excluding holidays.
 Networkdays DAX Formula
Network Days = NETWORKDAYS("01-01-2023","31-01-
2023",1,Holidays)

1 or omitted: Saturday,
Sunday 2: Sunday,
Monday
3: Monday, Tuesday
4: Tuesday, Wednesday
5: Wednesday, Thursday
6: Thursday, Friday
7: Friday, Saturday
11: Sunday only
12: Monday only
13: Tuesday only
14: Wednesday only
15: Thursday only
16: Friday only
17: Saturday only
 DATESINPERIOD DAX FORMULA

Formu
la
DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>,
<interval>)

The DATESINPERIOD function in DAX returns a single-column

table of dates within a specified period, based on a start date and
a defined interval. It’s useful when you need to create
calculations over dynamic date ranges (e.g., last 7 days,
previous month, etc.).
 DATESBETWEEN DAX FORMULA

Formu
la
DATESBETWEEN(<dates>, <start_date>,
<end_date>)

The DATESBETWEEN function in DAX returns a table with dates

within a specified start and end date range. This function is useful
when you want to filter data to specific date boundaries.
SAME PERIOD LAST YEAR DAX
FORMULA
Formu
la
SAMEPERIODLASTYEAR(<dates
>)

The SAMEPERIODLASTYEAR function in DAX is used to return a

table with the dates for the same period in the previous year.
This function is often used in time intelligence calculations to
compare current performance to the previous year.
 CALCULATE DAX
FORMULA
Formu
la
SAMEPERIODLASTYEAR(<dates
>)

The SAMEPERIODLASTYEAR function in DAX is used to return a

table with the dates for the same period in the previous year.
This function is often used in time intelligence calculations to
compare current performance to the previous year.
 TEXT FORMULAS
In Power BI, there are several DAX functions designed for
working with text data. These functions allow you to manipulate
and format text values in various ways. Here are some
commonly used text DAX formulas in Power BI:

LEFT, RIGHT, MID

LEFT("Power BI", 5) // Result:

"Power" RIGHT("Power BI", 2) //

Result: "BI" MID("Power BI", 7,

2) // Result: "BI"
 TEXT FORMULAS
In Power BI, there are several DAX functions designed for
working with text data. These functions allow you to manipulate
and format text values in various ways. Here are some
commonly used text DAX formulas in Power BI:

UPPER and LOWER

UPPER("Power BI") // Result:

"POWER BI" LOWER("Power BI") //
Result: "power bi"

LEN: Returns the length (number of characters)

of a text string.