In this Power BI Tutorial, we will discuss **Power Bi Time Intelligence Function** with examples. And also we discuss the below points:

- What is Power BI Time Intelligence Function
- List of Power BI Time Intelligence Function
- Power Bi Time Intelligence Function Examples

**At A Glance - Here's what we'll cover:**show

## What is Power BI Time Intelligence Function

The **Power Bi Time Intelligence Function that enables you **to manipulate data using time periods, including days, months, quarters, and years.

Then we can build and compare calculations over those periods in a Power Bi report.

## List of Power BI Time Intelligence Function

Here we will discuss the List of Power Bi Time Intelligence Function with description and syntax.

Function | Description | Syntax |
---|---|---|

CLOSINGBALANCEMONTH | This function evaluates the expression at the last date of the month in the current context | CLOSINGBALANCEMONTH( <expression>,<dates>[,<filter>]) |

CLOSINGBALANCEQUARTER | This function evaluates the expression at the last date of the quarter in the current context | CLOSINGBALANCEQUARTER (<expression>,<dates>[,<filter>]) |

CLOSINGBALANCEYEAR | This function evaluates the last date of the year in the current context | CLOSINGBALANCEYEAR (<expression>,<dates>[,<filter>][,<year end date>]) |

DATEADD | This function returns a table contains columns of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context. | DATEADD (<dates>,<number of interval>,<interval>) |

DATESBETWEEN | This function returns a table that contains a column of dates that begins with specified start dates and continues until a specified end date | DATESBETWEEN (dates,<start date>,<end date>) |

DATESINPERIOD | This function returns a table that contains a column of dates that begins with a specified start date and continues for the specified number and type of date interval | DATESINPERIOD (<dates>,<start date>,<number of interval>,<interval>) |

DATESMTD | This function returns a table that contains a column of the dates for the month to dates, in the current context | DATESMTD (<dates>) |

DATESQTD | This function returns a table that contains a column of the dates for the quarter to date, in the current context | DATESQTD (<dates>) |

DATESYTD | This function returns a table that contains a column of the dates for the year to date in the current context | DATESYTD (<dates>[<,Year end date>]) |

ENDOFMONTH | This function returns the last date of the month in the current context for the specified column of dates | ENDOFMONTH (<dates>) |

ENDOFQUARTER | This function returns the last date of the quarter in the current context for the specified columns of dates | ENDOFQUARTER (<dates>) |

ENDOFYEAR | This function returns the last date of the year in the current context for the specified columns of dates | ENDOFYEAR (<dates>[,<year end date>]) |

FIRSTDATE | This function returns the last date in the current for the specified column of dates | FIRSTDATE (<date>) |

FIRSTNONBLANK | This function returns the first value in a column, by filtering the current context, where the expression is not blank | FIRSTNONBLANK (<column>,<expression>) |

LASTDATE | This function returns the last date in the current context for the specified column | LASTDATE (dates) |

LASTNONBLANK | This function returns the last value in the column, filtered by the current context, where the expression is not blank | LASTNONBLANK (<column>,<expression>) |

NEXTDAY | This function returns a table that contains a column of all dates from the next day, based on the first day specified in the dates column in the current context | NEXTDAY (<dates>) |

NEXTMONTH | This function returns a table that contains a column of all dates from the next day, based on the first date in the date column in the current context | NEXTMONTH (<dates>) |

NEXTQUARTER | This function returns a table that contains a column of all dates in the next quarter, based on the first date in the date column, in the current context. | NEXTQUARTER (<dates>) |

NEXTYEAR | This function returns a table that contains a column of all dates in the next year, based on the first date in the dates column in the current context | NEXTYEAR (<dates>[,<year end date]) |

OPENINGBALANCEMONTH | This function evaluates the expression at the first date of the month in the current context. | OPENINGBALANCEMONTH (<expression>,<dates>[, <filter>]) |

OPENINGBALANCEQUARTER | This function evaluates the expression at the first date of the quarter in the current context | OPENINGBALANCEQUARTER (<expression>,<date>[,<filter>] |

OPENINGBALANCEYEAR | This function evaluates the expression at the first date of the year in the current context | OPENINGBALANCEYEAR (<expression>,<dates>[,<filter>[,<year end date>]) |

PARALLELPERIOD | This function returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context with the dates shifted a number of intervals either forward in time or backward in time. | PARALLELPERIOD (<dates>,<number of interval>,<interval>) |

PREVIOUSDAY | This function returns a table that contains a column of all dates representing the day that is previous to the first date in the date column, in the current context. | PREVIOUSDAY (<dates>) |

PREVIOUSMONTH | This function returns a table that contains a column of all dates from the previous month, based on the first date in the dates column, in the current context. | PREVIOUSMONTH (<dates>) |

PREVIOUSQUARTER | This function returns a table that contains a column of all dates from the previous quarter, based on the first dates column in the current context. | PREVIOUSQUARTER (<dates>) |

PREVIOUSYEAR | This function returns a table that contains a column of all dates in the previous year, given the last dates in the dates column, in the current context | PREVIOUSYEAR (<dates>,[,<year end date>]) |

STARTOFMONTH | This function returns the first date of the month in the current context for the specified columns of dates | STARTOFMONTH (<dates>) |

STARTOFQUARTER | This function returns the first date of the quarter in the current context for the specified columns of dates | STARTOFQUARTER (<dates>) |

STARTOFYEAR | This function returns the first date of the year in the current context for the specified column of dates | STARTOFYEAR (<dates>) |

TOTALMTD | This function evaluates the value of the expression for the month to date in the current context | TOTALMTD (<expression>,<dates>[,<filter>]) |

TOTALQTD | This function evaluates the value of the expression for the dates in the quarter to dates, in the current context | TOTALQTD (<expression>,<dates>[,<filter>]) |

TOTALYTD | This function evaluates year to date value of the expression in the current context | TOTALYTD (<expression>,<dates>[,<filter>][,<year end date>]) |

Read Power Bi Text Functions with Examples

## Power Bi Time Intelligence Function Example

Here we will how to use different types of time intelligence functions with examples.

### Example 1: Using Power Bi CLOSINGBALANCEMONTH Function

Here we will see how to use CLOSINGBALANCEMONTH Function with example in the Power Bi.

We will use the below balance sheet sample data having two columns i.e. Date column and balance columns.

So, now we will create a calculated measure that will calculate the closing balance for every month.

Here for June month, the function will return the closing balance 630, similarly, the function will return the close balance for every month.

Click on the New Measure from the ribbon, then write the below measure:

`CBMonth = CLOSINGBALANCEMONTH(SUM(balanceSheet[Balance]), 'balanceSheet'[Date])`

Read Power BI Information Functions

### Example 2: Using Power BI CLOSINGBALANCEQUARTER Function

Here we will see how to use CLOSINGBALANCEQUARTER function with example in Power Bi.

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure that will calculate the closing balance for every quarter.

So for April-June, we have balance data for June month So, it will show 630, and for July- September, it will show 3200.

So click on the new measure from the ribbon in Power Bi desktop, then write the below measure:

`CBQ = CLOSINGBALANCEQUARTER(SUM(balanceSheet[Balance]), 'balanceSheet'[Date])`

Also read, Power Bi count function – DAX Examples

### Example 3: Using Power BI CLOSINGBALANCEYEAR Function

Here we will see how to use **CLOSINGBALANCEYEAR Function in Power BI**.

We will use the below sample table, having two columns i.e. date and balance columns.

So here we will create a measure that will calculate the closing balance for every year.

So we have only one year in sample data, so it will show the last balance in the year i.e. 3200, which is the last balance of the year.

`CBQ = CLOSINGBALANCEYEAR(SUM(balanceSheet[Balance]), 'balanceSheet'[Date])`

Read Power BI average function with Examples

### Example 4: Using Power Bi DATEADD Function

Here we will see how to use the DATEADD function with an example in Power Bi.

We will use the below sample data, having two columns i.e. date and balance columns.

Now we will create a calculated column, to calculate the dates that are this year, based on your number of intervals i.e. 0.

So here it will show the data present in the date column, if it is not there then it will show blank.

`DateThisYear = DATEADD(balanceSheet[Date],0,YEAR)`

Read Power BI MAX and MIN function with Examples

### Example 5: Using Power Bi DATESBETWEEN Function

Here we will see how to use DATESBETWEEN function with example in Power Bi desktop.

We will use the below sample data, having two columns i.e. date and balance columns.

Now we will create a measure, in which we will calculate the balance for two months by using the DATESBETWEEN function.

```
Total balance for 2 months =
CALCULATE(
SUM(balanceSheet[Balance]),
DATESBETWEEN(
'balanceSheet'[Date] ,
DATE(2021,07,1),
DATE(2021,08,30 )
))
```

Read Power BI DAX Logical functions

**Example 6: Using Power BI DATESINPERIOD function**

Here we will see how to use the DATESINPERIOD function with example in Power Bi desktop

We will use the below sample data, having two columns i.e. date and balance columns.

So, now we will create a measure, which we will calculate the total balance for September using the DATESINPERIOD function.

```
Total balance in september =
CALCULATE(
SUM(balanceSheet[Balance]),
DATESINPERIOD(balanceSheet[Date],DATE(2021,09,1),1,YEAR)
)
```

Read Power BI DAX SUM and SUMX function

### Example 7: Using Power Bi DATESMTD Function

Here we will see how to use DATESMTD Function with example in Power BI Desktop.

We will use the below sample data, having two columns i.e. date and balance columns.

Now we will create a measure, which will calculate the total month-to-date balance. So, for September =12300

```
Total balance for MTD =
CALCULATE(
SUM(balanceSheet[Balance]),
DATESMTD(balanceSheet[Date])
)
```

Read Power bi measure examples (20 useful examples)

**Example 8: Using Power BI DATESQTD Function**

Here we will see how to use the DatesQTD function with example in Power Bi.

We will use the below sample table, having two columns i.e. date and balance columns.

Now, we will create a measure in which we will calculate the total quarter-to-date balance, so for July-September total balance is 39168.

```
Total balance for QTD =
CALCULATE(
SUM(balanceSheet[Balance]),
DATESQTD(balanceSheet[Date])
)
```

Read Power bi measure count with filter

### Example 9: Using Power BI Total DATESYTD Function

Here we will see how to use DATESYTD Function with example in Power Bi.

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure, in which we will calculate the year-to-date balance, so it will calculate the total balance of each year.

```
Total balance for YTD =
CALCULATE(
SUM(balanceSheet[Balance]),
DATESYTD(balanceSheet[Date])
)
```

Read How to get selected value from Slicer in Power BI

### Example 10: Using Power BI ENDOFMONTH Function

Here we will see how to use the ENDOFMONTH function using Power Bi.

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure, which will show the amount present at the end of the month. So here, it will show the current amount at the end of the month i.e. 3200.

```
Total balance for EOM =
CALCULATE(
SUM(balanceSheet[Balance]),
ENDOFMONTH(balanceSheet[Date])
)
```

Read Power BI Measure multiply with examples

### Example 11: Using Power Bi ENDOFQUARTER Function

Here we will see how to use the **ENDQUARTER function in power Bi**.

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure, which will calculate the amount at the end of the quarter.

So here is the end of the quarter is 30th June and 10th September, the current amount is 630 and 3200 respectively.

```
Total balance for EOQ =
CALCULATE(
SUM(balanceSheet[Balance]),
ENDOFQUARTER(balanceSheet[Date])
)
```

### Example 12: Using Power Bi ENDOFYEAR Function

Here we will see how to use the ENDOFYEAR function in Power Bi.

We will use the below sample table, having two columns i.e. date and balance columns.

Now, we will create a measure, which will calculate the last date balance of the current year using the ENDOFYEAR function. So here is the last date balance of the year is 3200

```
balance for EOY =
CALCULATE(
SUM(balanceSheet[Balance]),
ENDOFYEAR(balanceSheet[Date])
)
```

### Example 13: Using Power BI FIRSTDATE function

Here we will see how to use the FIRSTDATE function in Power BI.

We will use the below sample data, having two columns i.e. date and balance columns

Now we will use the measure, to show the balance on the first date of the date column. So here the first date is 2nd June 2021 the balance is 100

```
balance in first date =
CALCULATE(
SUM(balanceSheet[Balance]),
FIRSTDATE(balanceSheet[Date])
)
```

### Example 14: Using Power Bi LASTDATE Function

Here we will see how to use **LASTDATE Function in Power Bi**.

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure which will calculate the balance present in the last date of the date column. So here 10th sept 2021 is the last date in the date column, the balance is 3200.

```
balance in last date =
CALCULATE(
SUM(balanceSheet[Balance]),
LASTDATE(balanceSheet[Date])
)
```

### Example 15: Using Power Bi NEXTDAY Function

Here we will see how to use NEXTDAY Function in Power Bi.

We will use the below sample table, having two columns i.e. date and balance columns.

Now, we will create a measure which will show the balance of the next day based on the first date of the date column.

For example in 8 the sept this function will return the balance of next day i.e. 3000. And if there is no next day then it will return blank, for example after 2nd sept 2021, 3rd sept is not present in the date column, so it will return blank.

```
balance in Next day =
CALCULATE(
SUM(balanceSheet[Balance]),
NEXTDAY(balanceSheet[Date]))
```

Read Power bi measure switch statement

### Example 16: Using Power BI NEXTMONTH Function

Here we will see how to use NEXTMONTH Function in Power BI.

We will use the below sample data, having two columns i.e. date and balance columns.

Now, we will create a measure, which will show the next month total balance after the first date in the date column.

For example if we will calculate the total balance of next month of August i.e September, then it will return 14300. And if there is no next month to that month then it will return blank.

```
balance in Next day =
CALCULATE(
SUM(balanceSheet[Balance]),
NEXTMONTH(balanceSheet[Date]))
```

Read Countif function in Power BI Measure

### Example 17: Using Power Bi NEXTQUARTER Function

Here we will see how to use NEXTQUARTER FUNCTION in Power Bi.

We will use the below sample data, having two columns i.e. date and balance columns.

Now we will create a measure which will calculate the total balance of next quarter.

For example here we have two quarter April- June and July -September data, so we have june month data is there from that quarter, if we calculate the total balance of the next quarter i.e. July- September is 41168.

```
balance in Next Quaerter =
CALCULATE(
SUM(balanceSheet[Balance]),
NEXTQUARTER(balanceSheet[Date]))
```

Read How to create a Measure based on Slicer in Power BI

### Example 18: Using Power Bi OPEANINGBALANCEMONTH Function

Here we will see how to use OPEANINGBALANCEMONTH function in Power Bi.

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure, which will show the opening balance of the month.

For example for July month the opening balance is 630, similarly for august month the opening balance is 942 and so on.

```
opeaning balance of month =
OPENINGBALANCEMONTH(
SUM(balanceSheet[Balance]),
balanceSheet[Date])
```

Read Power BI Measure SUM

### Example 19: Using Power Bi OPEANINGBALANCEQUARTER function

Here we will see how to use OPEANINGBALANCEQUARTER Function in Power Bi

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure, which will show the opening balance of each quarter. For example the opening balance of July – September is 630

```
opeaning balance of Quarter =
OPENINGBALANCEQUARTER(
SUM(balanceSheet[Balance]),
balanceSheet[Date])
```

Read Power bi measure by category

### Example 20: Using Power Bi PARALLELPERIOD Function

Here we will see how to use Power Bi PARALLELPERIOD Function

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure, which will calculate the total balance of previous month using PARALLELPERIOD function.

For example, in September it will show the total balance of August month i.e 7655.

`Parallel preiod balance = CALCULATE(SUM(balanceSheet[Balance]), PARALLELPERIOD(balanceSheet[Date],-1,MONTH))`

Read Power bi measure subtract

### Example 21: Using Power Bi PREVIOUSDAY Function

Here we will see how to use PREVIOUSDAY Function in Power Bi

We will use the below sample data, having two columns i.e. date and balance columns.

Now we will create a measure which we will calculate the balance of the previous day and if there is no previous day then the function return blank

For example, for 9th septempber the previous balance is 2500 but for 8th september, it will return blank because there is no data for 7 th september.

`Previous day balance = CALCULATE(SUM(balanceSheet[Balance]), PREVIOUSDAY('balanceSheet'[Date]))`

### Example 22: Using Power Bi PREVIOUSMONTH Function

Here we will see how to use PREVIOUSMONTH Function in Power Bi.

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure which we will calculate the total balance of the previous month.

For example if we calculate the previous month balance of september i.e august total balance is 7655

`Previous month balance = CALCULATE(SUM(balanceSheet[Balance]), PREVIOUSMONTH('balanceSheet'[Date]))`

Read Power BI Measure IF with Examples

### Example 23: Using Power Bi PREVIOUSQUARTER FUNCTION

Here we will see how to use PREVIOUSQUARTER Function in Power Bi.

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure, which we will calculate the total balance of the previous Quarter.

For example, as per our data if we calculate the total balance of previous quarter of july- september is 17090

### Example 24: Using Power Bi PREVIOUSYEAR FUNCTION

Here we will see how to use PREVIOUSYEAR Function in Power Bi.

We will use the below sample table, having two columns i.e. date and balance columns.

Now we will create a measure which will calculate the previous year total balance.

So, in our dataset we have 2 year i.e. 2020 and 2021, so we will show the previous year i.e. 2020 balance is

`Previous Year balance = CALCULATE(SUM(balanceSheet[Balance]),PREVIOUSYEAR(balanceSheet[Date]))`

Read How to create a filter in Power bi

### Example 25: Using Power BI STARTOFMONTH Function

Here we will see how to use STARTOFMONTH Function in Power Bi.

We will use the below sample data, having two columns i.e. date and balance columns.

Here we will create a measure, which will show the balance of start date of month. In the date column the starting date of December 2020 month is 28 December, so the balance is 200.

`Start of month balance = CALCULATE(SUM(balanceSheet[Balance]),STARTOFMONTH(balanceSheet[Date]))`

Read Power bi show items with no data

### Example 26 : Using Power Bi STARTOFQUARTER Function

Here we will see how to use STRTOFQUARTER Function in Power bi.

We will use the below sample data, having two columns i.e. date and balance columns

Now we will create a measure, which will show the balance of first date of each quarter.

`Start of quarter balance = CALCULATE(SUM(balanceSheet[Balance]),STARTOFQUARTER(balanceSheet[Date]))`

Read Power bi show value as percentage

### Example 27: Using Power Bi STARTOFYEAR Function

Here we will see how to use STARTOFYEAR function in Power Bi.

We will use the below sample data, having two columns i.e. date and balance columns.

Here we will create a measure which will show the balance present the start date of the year using measure.

For example the start date of the 2021 in sample data is 2nd January 2021 and the balance is 500

`Start of Year balance = CALCULATE(SUM(balanceSheet[Balance]),STARTOFYEAR(balanceSheet[Date]))`

Read How to create a Power BI Dashboard in Microsoft teams

### Example 28: Using Power Bi TOTALMTD Function

Here we will see how to use ToTALMTD Function in Power Bi.

We will use the below sample data, having two columns i.e. date and balance columns.

Here we will create a measure, which will calculate the month running total balance.

For example, for December month, it will sum the balance i.e. 200+300+400+500=1400

`Total MTD balance = TOTALMTD(SUM(balanceSheet[Balance]),'balanceSheet'[Date])`

Read Power bi sum group by multiple columns

### Example 29: Using Power Bi TOTALQTD Function

Here we will see how to use TOTALQTD Function in Power Bi.

We will use the below sample data, having two columns i.e. date and balance columns.

Here we will create a measure, which will calculate the quarter running total balance by using TOTALQTD function.

`Total QTD balance = TOTALQTD(SUM(balanceSheet[Balance]),'balanceSheet'[Date])`

### Example 30: Using Power Bi TOTALYTD Function

Here we will see how to use TOTALYTD Function in Power Bi.

We will use the below sample data, having two columns i.e. date and balance columns

Here we will create a measure, which will calculate the year running total balance by using TOTALYTD function

`Total YTD balance = TOTALYTD(SUM(balanceSheet[Balance]),'balanceSheet'[Date])`

Read Power bi date format

In this Power Bi tutorial, we learned Power Bi Time Intelligence Function with examples. And also we discuss the below points:

- What is Power BI Time Intelligence Function
- List of Power BI Time Intelligence Function
- Power Bi Time Intelligence Function Example

Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com