Time Intelligence Functions in Power BI Dax
Introduction –
Time Intelligence Functions - Does the name scare you?
Don’t worry, Microsoft Power BI have some built-in functions that helps to manipulate Time Period related data. You can use these measures for further calculations.
Let us see what Power BI offers and try them out with our Dataset.
Here I am using Superstore Dataset. It has Profit and Sales data for the Orders made and it ranges from Jan 2019 to Dec 2021.
Power BI Offers 35 Time Intelligence Functions. I have categorized them as follows,
Category | Function |
Previous | PREVIOUSDAY |
| PREVIOUSMONTH |
| PREVIOUSQUARTER |
| PREVIOUSYEAR |
NEXT | NEXTDAY |
| NEXTMONTH |
| NEXTQUARTER |
| NEXTYEAR |
START | STARTOFMONTH |
| STARTOFQUARTER |
| STARTOFYEAR |
END | ENDOFMONTH |
| ENDOFQUARTER |
| ENDOFYEAR |
First | FIRSTDATE |
| FIRSTNONBLANK |
LAST | SAMEPERIODLASTYEAR |
| LASTDATE |
| LASTNONBLANK |
Opening | OPENINGBALANCEMONTH |
| OPENINGBALANCEQUARTER |
| OPENINGBALANCEYEAR |
Closing | CLOSINGBALANCEMONTH |
| CLOSINGBALANCEQUARTER |
| CLOSINGBALANCEYEAR |
Total | TOTALMTD |
| TOTALQTD |
| TOTALYTD |
DATE | DATEADD |
| DATESBETWEEN |
| DATESINPERIOD |
| DATESMTD |
| DATESQTD |
| DATESYTD |
| PARALLELPERIOD |
These Functions can be used along with CALCULATE function to get desire results.
Let us see how to use these functions in detail,
Category – PREVIOUS
Functions in this Category provides Previous Period value. This Period can be Day, Month, Quarter or a Year
Code Format –
MEASURE_NAME = CLACULATE ((EXPRESSION, filters_if_any…), PREVIOUSDAY/MONTH/QUARTER/YEAR(DATE_FIELD FROM UR TABLE/ DATE_MEASURES.ATTRIBUTE_YOU WANT TO EXTRACT FROM [DATE/MONTH/QUARTER/YEAR]))
Function - PREVIOUS DAY
Code Snippet –
PriviousDayProfit = CALCULATE(sum(Orders[Profit]),
PREVIOUSDAY(Orders[Order Date].[Date]))
Select TABLE visual and drag Order Date and Profit into it.
Click on NEW MEASURE
Rename new Measure as PriviousDayProfit and write a query as shown in below screenshot.
Drag and drop this new Measure into the table
It shows the Previous Day Profit
This function can also help to find Difference between 2 consecutive day’s profit
Function - PREVIOUS Month
PowerBI offers PreviousMonth function which gives all dates from the previous month, based on the first date in the Dates column, in the current context.
Let us try this with our dataset
Here again select a Table Visualization and drag Order DATE and Profit into it. This time level-up to Months.
Then create New Measure and write following formula to get Previous Month profit.
Drag and drop this new measure into the table. And here you go… It gives you previous month’s profit.
Similarly You can work on Previous Quarter and Previous Year functions.
Function - Previous Quarter
Code Snippet -
PerviousQuarterProfit = CALCULATE(sum(Orders[Profit]),
PREVIOUSQUARTER(Orders[Order Date].[Date]))
Create New Measure as shown below.
Drag and drop this new Measure into the table
Function - Previous Year –
Code Snippet -
PerviousYearProfit = CALCULATE(SUM(Orders[Profit]),
PREVIOUSYEAR(Orders[Order Date].[Date]))
Create New Measure as shown below.
Drag and drop this new Measure into the table
Category – NEXT
Functions in this Category provides NEXT Period value. This Period can be Day, Month, Quarter or a Year
Code Format –
MEASURE_NAME = CLACULATE ((EXPRESSION, filters_if_any…), NEXTDAY/MONTH/QUARTER/YEAR(DATE_FIELD FROM UR TABLE/ DATE_MEASURES.ATTRIBUTE_YOU WANT TO EXTRACT FROM [DATE/MONTH/QUARTER/YEAR]))
Function - NEXTQUARTER
Code Snippet –
NextQuarterProfit = CALCULATE(sum(Orders[Profit]),
NEXTQUARTER(Orders[Order Date].[Date]))
Select TABLE visual and drag Order Date and Profit into it.
Click on NEW MEASURE
Function - NEXTYEAR
Code Snippet –
NextYearProfit = CALCULATE(SUM(Orders[Profit]),
NEXTYEAR(Orders[Order Date].[Date]))
Select TABLE visual and drag Order Date and Profit into it.
Level up Order Date upto Year
Click on NEW MEASURE
Write a function as given above. Then Drag this new measure into the table and here you go…it summarize next year’s profit in just one step.
Category – Start
Function – STARTOFMONTH
This function returns first date of the month in the current context for the specified column of dates.
Code Snippet –
MEASURE_NAME= STARTOFMONTH(DATE_COL_FROM_TABLE)
StartOfMonth = STARTOFMONTH(Orders[Order Date].[Date])
Create New Measure as shown below.
Drag and drop this new Measure into the table
Similarly, you can calculate STARTOFQUARTER & STARTOFYEAR
StartOfQuarter = STARTOFQUARTER(Orders[Order Date].[Date])
StartOfYear = STARTOFYEAR(Orders[Order Date].[Date])
Category – END
Function – ENDOFMONTH
This function returns Last date of the month in the current context for the specified column of dates.
Code Snippet –
MEASURE_NAME= ENDOFMONTH(DATE_COL_FROM_TABLE)
Create New Measure as shown below.
Drag and drop this new Measure into the table
On the same line one can calculate ENDOFQUARTER and ENDOFYEAR values
These measures can be used for further analysis
Category – DATE
Function – DATEADD
Code Snippet –
DATEADD ( DATE_COL, NUMBER_OF_INTERVALS,INTERVAL)
Here, if you want to calculate in future compare to today’s the give NUMBER_OF_INTERVALS as a Positive Number.
if you want to calculate in back in time compare to today’s the give NUMBER_OF_INTERVALS as a Negative Number.
Types of Intervals are – Day, Month, Quarter and Year
So one can calculate it as per the need.
Let us see with an example –
Forward In Time -
Create New Measure as shown below.
Drag and drop this new Measure into the table
DATEADD = CALCULATE(SUM(Orders[Profit]),
DATEADD(Orders[Order Date].[Date],5,DAY))
Backward in Time –
Create New Measure as shown below.
Drag and drop this new Measure into the table
DATADD_PREV = CALCULATE(SUM(Orders[Profit]),DATEADD(Orders[Order Date].[Date],-5,DAY))
Function - DATESBETWEEN
Returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date.
CODE Snippet -
DATESBETWEEN(<Date>, <StartDate>, <EndDate>)
Here,
Date - is Date Column
Start Date is Date Expression
End Date - Date Expression
Function - DATESINPERIOD
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 intervals.
This function is suited to pass as a filter to the CALCULATE function. Use it to filter an expression by standard date intervals such as days, months, quarters, or years
Code Sinppet
DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>
Dates - is Date Col
Start_Date is a date expression
Number of Interval - An integer that specifies the number of intervals to add to, or subtract from, the dates.
Interval - The interval by which to shift the dates. The value for interval can be one of the following: DAY, MONTH, QUARTER, and YEAR
Conclusion -
These DAX Time Intelligence Functions helps to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.
In simple words, these Functions make life easy for difficult Time Related Calculations.