Power BI's data analysis functions, especially time-based analysis, are designed to help you analyze data over different time periods. These functions are commonly known as Time Intelligence functions. Below is a list of some important Time Intelligence functions and their usage:
.jpg) |
| Data analysis functions |
1. TOTALYTD
- Usage: Used to calculate the Year-to-Date
(YTD) total, such as total sales from the beginning of the year to the current
date.
Syntax:
```
DAX TOTALYTD(expression, dates[, filter[,
year_end_date]])
```
Example:
```
DAX Total Sales YTD =
TOTALYTD(SUM(Sales[Amount]), Dates[Date])
```
2. TOTALQTD
- Usage: Used to calculate the
Quarter-to-Date (QTD) total, such as total sales from the beginning of the
quarter to the current date.
Syntax:
```
DAX TOTALQTD(expression, dates[, filter[,
year_end_date]])
```
Example:
```
DAX Total Sales QTD =
TOTALQTD(SUM(Sales[Amount]), Dates[Date])
```
3. TOTALMTD
- Usage: Used to calculate the Month-to-Date
(MTD) total, such as total sales from the beginning of the month to the current
date.
Syntax:
```
DAX TOTALMTD(expression, dates[, filter])
```
Example:
```
DAX Total Sales MTD =
TOTALMTD(SUM(Sales[Amount]), Dates[Date])
```
4. SAMEPERIODLASTYEAR
- Usage: Used to compare data from the same
period last year.
Syntax:
```
DAX SAMEPERIODLASTYEAR(dates)
```
Example:
```
DAX Sales LY = CALCULATE(SUM(Sales[Amount]),
SAMEPERIODLASTYEAR(Dates[Date]))
```
5. DATEADD
- Usage: Used to add or subtract a specific
number of time intervals.
Syntax:
```
DAX DATEADD(dates, number_of_intervals,
interval)
```
Example:
```
DAX Sales Last Month =
CALCULATE(SUM(Sales[Amount]), DATEADD(Dates[Date], -1, MONTH))
```
6. DATESYTD
- Usage: Provides a list of dates from the
start of the year to the current date.
Syntax:
```
DAX DATESYTD(dates[, year_end_date])
```
Example:
```
DAX Dates YTD = DATESYTD(Dates[Date])
```
7. DATESQTD
- Usage: Provides a list of dates from the
start of the quarter to the current date.
Syntax:
```
DAX DATESQTD(dates)
```
Example:
```
DAX Dates QTD = DATESQTD(Dates[Date])
```
8. DATESMTD
- Usage: Provides a list of dates from the
start of the month to the current date.
Syntax:
```
DAX DATESMTD(dates)
```
Example:
```
DAX Dates MTD = DATESMTD(Dates[Date])
```
9. PREVIOUSMONTH
- Usage: Provides the dates of the previous
month.
Syntax:
```
DAX PREVIOUSMONTH(dates)
```
Example:
```
DAX Sales Last Month =
CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH(Dates[Date]))
```
10. PREVIOUSQUARTER
- Usage: Provides the dates of the previous
quarter.
Syntax:
```
DAX PREVIOUSQUARTER(dates)
```
Example:
```
DAX Sales Last Quarter =
CALCULATE(SUM(Sales[Amount]), PREVIOUSQUARTER(Dates[Date]))
```
11. PREVIOUSYEAR
- Usage: Provides the dates of the previous
year.
Syntax:
```
DAX PREVIOUSYEAR(dates)
```
Example:
```
DAX Sales Last Year =
CALCULATE(SUM(Sales[Amount]), PREVIOUSYEAR(Dates[Date]))
```
12. FIRSTDATE
- Usage: Provides the first date in a
specific context.
Syntax:
```
DAX FIRSTDATE(dates)
```
Example:
```
DAX First Sales Date =
FIRSTDATE(Sales[Date])
```
13. LASTDATE
- Usage: Provides the last date in a
specific context.
Syntax:
```
DAX LASTDATE(dates)
```
Example:
```
DAX Last Sales Date = LASTDATE(Sales[Date])
```
14. WEEKDAY
- Usage: Determines the day of the week for
a given date.
Syntax:
```
DAX WEEKDAY(date[, return_type])
```
Example:
```
DAX Day of Week = WEEKDAY(Sales[Date])
```
15. YEARFRAC
- Usage: Calculates the fraction of the year
between two dates.
Syntax:
```
DAX YEARFRAC(start_date, end_date[, basis])
```
Example:
```
DAX Year Fraction = YEARFRAC(Sales[Start
Date], Sales[End Date])
```
By using
these Time Intelligence functions, you can analyze your data over different
time periods and create reports based on various time frames.
.jpg&description=Data analysis functions - AnalysisWithSadiul){kind=link}