Using Power BI Report Builder expressions is an advanced topic that requires a little programming knowledge. You can use if clauses, cases, functions, etc.
In this tutorial, we will introduce the use of Expressions in Power BI Report Builder. We will show especially the Date functions.
Requirements for Power BI Report Builder expressions
- First of all, you need Power BI Report Builder.
- Secondly, the internet to follow this tutorial.
Getting started with Power BI Report Builder expressions
First, we will work with some basic dates. In a report, in the title right-click and select Expressions.
The first example will be a Hello world.
Secondly, run the report to check the results.
Also, you will be able to see the results.
Finally, you can check some simple examples.
|=”Hello world”||Hello world||First, we have a simple string example that shows a hello world.|
|=“Hello”+space(1)+”world”||Hello world||Secondly, we are showing how to use a space. It is a good practice to use the space function instead of a simple space with quotes because the SPACE function is more visible.|
|=“Hello”+ vbcrlf +”world”||Hello|
|Finally, we are showing how to display the second line using the vbcrlf. In this example, the word world is in the second line.|
Functions in Power BI Report Builder Expressions
In order to check the date functions, go to Common Functions>Date and time. You can see a description and examples here.
Let’s take a look to some examples. These examples were run on 10 Mar 2022.
|=TODAY()||5/10/2022 12:00:00 AM||First, we have this function that shows the current date.|
|=NOW()||5/10/2022 5:52:33 AM||Secondly, the now function shows the current date and time.|
|=YEAR(TODAY())||2022||Thirdly, the year function shows the year of a specified date.|
|=Month(TODAY())||5||Also, the month function shows the month of a date.|
|=DAY(TODAY())||10||In addition, the day function will show the day of a specified date.|
|=HOUR(NOW())||17||Another function is the hour which shows the hour of a time value.|
|=MINUTE(NOW())||52||Also, we have the minute function to get the minutes from a time value.|
|=”This is the current date ”+CStr(TODAY())||This is the current date 5/10/2022||In this example, we are concatenating a string with a date. We use the function CString to convert from date to string.|
|=”This is the current date ”+DateString ()||This is the current date 05/10/2022||In addition, DateString converts the date into a string. In this example, we are concatenating a string with a date.|
|=Cdate(“5-2-20”)||5/02/2020 12:00:00 AM||Cdate converts the data into a date.|
|=Globals!ExecutionTime||5/10/2022 5:52:33 AM||Finally, Globals!ExecutionTime displays the date and time of the Report execution. .|
The FormatDateTime function used as a Power BI Report Builder expression
The formatDateTime function has several options. We will explain each of them.
|=FormatDateTime(Now, DateFormat.ShortDate||5/10/2022||First, we have a short date format.|
|=FormatDateTime(Now, DateFormat.LongDate)||Tuesday, May 10, 2022||Secondly, we have the long date format.|
|=FormatDateTime(Now, DateFormat.GeneralDate)||5/10/2022 5:52:33 AM||Thirdly, we have the general date format which includes the time.|
|=FormatDateTime(Now, DateFormat.LongTime)||5:52:33 AM||Also, we can specify just the time in a long format.|
|=FormatDateTime(Now, DateFormat.ShortTime)||5:52||Finally, the function can provide a short time format.|
Day of week function in Power BI Report Builder expression
|=WeekdayName(1,False)||Sunday||First, we will show the WeekdayName number 1 which is Monday.|
|=WeekdayName(2,False)||Monday||The second-week day's name is Monday.|
|=WeekdayName(3,False)||Tuesday||Also, the third day is Tuesday and so on.|
|=WeekdayName(2,True)||Mon||In addition, the second parameter shows if it is abbreviated (True) or not (false).|
|=WeekdayName(2,True,3)||Wed||Finally, the third parameter is the first day of the week. 1 is Sunday, 2 is Monday, and so on. The default is 0.|
MonthName function in Power BI Report Builder Expressions
In addition, we have the MonthName function
|=MonthName(Month(Cdate("8/10/2022")),False))||August||First, we have the MonthName function that returns the name of a month. This function requires the month number.|
|=MonthName(Month(Cdate("8/10/2022")),True)||Aug||Secondly, this example shows the month name in abbreviated format.|
|=UCase(MonthName(Month(Cdate("8/10/2022")),True))||AUG||Thirdly, this example shows the month name uppercased.|
|=LCase(MonthName(Month(Cdate("8/10/2022")),True))||aug||Finally, this example shows the month name lowercased.|
Datediff and Dateadd
Finally, we will show some examples of the Datediff and DateAdd functions.
|=DateAdd("d",3,"5/10/2022")||5/13/2020 12:00:00 AM||First, we have the DateAdd function. In this example, we add 3 days to the 5/10/2022 date.|
|=DateAdd("m",3,"5/10/2022")||8/10/2020 12:00:00 AM||Secondly, we add 3 months to the 5/10/2022 date.|
|=DateAdd("yyyy",3,"5/10/2022")||5/10/2025 12:00:00 AM||Thirdly we add 3 years to the 5/10/2022 date.|
|=DateAdd("q",3,"5/10/2022")||2/10/2023 12:00:00 AM||Also, we add 3 quarters to the 5/10/2022 date.|
|=DateDiff("h","5:10:22","3:10:22")||-2||In addition, we have the DateDiff. This example shows the difference in hours between 2 times.|
|=DateDiff("n","5:10:22","5:08:22")||-2||The next example, shows the difference in minutes between 2 times.|
|=DateDiff("s","5:10:22","5:10:32")||10||Finally, we have the difference in seconds between 2 times.|
Conclusion about the Power BI Report Builder Expressions
In this article, we learn the main Power BI Report Builder expressions. We focused on date functions, but we will have more examples in the next articles.