top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-

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.



119 views0 comments

Recent Posts

See All

Comments

Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page