# Power Bi Time Intelligence Function + 30 Examples

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

## 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.

## 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])``

### 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])``

### 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)``

### 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 )
))``````

### 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)
)``````

### 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])
)``````

### 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])
)``````

### 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])
)``````

### 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])
)``````

### 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]))``````

### 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]))``````

### 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]))``````

### 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])``````

### 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])``````

### 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))``

### 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]))``

### 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]))``

### 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]))``

### 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]))``

### 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]))``

### 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])``

### 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

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