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