• Home
  • Formatting in Power BI: Part 3

Formatting in Power BI: Part 3

  • admin
  • July 1, 2022

In this article we are going to understand how to format date in Power BI.

I have loaded a sample dataset from Power BI itself by going over on “Load sample data” and clicking the button. It has all the financials data in it. It also includes dates. And that’s why we’re going to work with this dataset in this article.

The following is the dataset we are going to work with today:

You can see how it includes all sorts of data from date, country, month name to sales, profits earned, units sold, etc. Suppose we want to see only the dates column, how would we do that? Good news for you, everything in Power BI has its own little shortcut!

Viewing a Specific Column in Power BI

So if we want to see only the “Date” column from that entire table you just saw. We would first need to create a new table by heading over onto “Modeling” Tab and then “New Table.”

The following is what we would enter:

Sample Dates = DISTINCT(financials[Date].[Date])

The Distinct function in Power BI provides a one-column table with the requested column’s unique values as its return value. It basically removes duplicate values and returns only the unique values.

So when we enter this, the following would happen:

Now you can see that we have successfully received ONLY the date column as we wanted. However, it is also showing us the time “00:00:00” which seems pretty unnecessary to us right now since we only wanted dates. We can remove this too!

In order to do this, right-click on the table and the “Column tools” tab will appear right next to “Table tools.” Change the Data type to “Date.”

Once you have done that, the column will be formatted to only dates and it will not show you the time.

General Format of Dates

Now if we wanted to see the general format. We would first create a new column right next to the previous column. In order to do this, Click “New Column” from “Table tools.”

We would enter the following in there:
General Dates = FORMAT([Date], “General Date”)
This is the general format that is used by Power BI. It goes by MM/DD/YYYY.

The following image below is the result.

This arrangement of dates can also be derived by the following formula:

General Dates = FORMAT([Date], “Short Date”)

The same result would appear if you entered that.

This arrangement, however, requires particular attention to the date format, whether it is DD/MM/YYYY or MM/DD/YYYY. This might also cause you trouble if you misinterpreted it. In order to avoid this, you can convert the date order into an expanded version. Basically where it isn’t just numbers.
You can do so by replacing “General Date” with “Long Date”

The following is what your command should look like:
General Dates = FORMAT([Date], “Long Date”)

Now instead of showing 1/1/2013, it shows “Tuesday, January 1, 2013” and so on for the rest of the dataset. However, now it is also showing you the days of the week along with the date, month and year. You can minimize this format by replacing “Long Date” with “Medium Date” from the formula.

The following is what your command should look like:
General Dates = FORMAT([Date], “Medium Date”)

The following image below is the result.

This formula according to me is the best since it wouldn’t confuse me with the numbers and since it isn’t a super expanded version either.

What If You Only Wanted to See the Dates?

It could be possible that your dataset is from the same month & year and since that is not a concern, you really just want to see the dates and nothing more or less. Maybe you are a student and you want to see how many days and which day for the month of January, you went to college.

You can do so by entering the following formula:

General Dates = FORMAT([Date], “d”)

Now I can see only the dates from the dataset.

Some Other Formulas to Experiment

The following are some other silly formulas to try along with its functions:

General Dates = FORMAT([Date], “dd”)

This will add a “0” before the date. For example, if the date was “1,” it would be formatted as “01,” and so on.

General Dates = FORMAT([Date], “m”)

Incase you wanted to see only the month instead of dates, this will do it for you. This will show you only the month from the column.

General Dates = FORMAT([Date], “mm”)

This will add a “0” before the month. (Same as how it did for dates previously)

General Dates = FORMAT([Date], “yyyy”)

This will show only the year from the column.

General Dates = FORMAT([Date], “yy”)

This will also only show the year from the column. However, if the year was “2013,” it would be formatted as “13,” and so on for all the other years in the dataset.

Merging Formulas

Now if you wanted to merge the above formulas, for example, to see both dates and months but not years from the dataset column, you could do so too!

The following is the formula you will need to enter:

General Dates = FORMAT([Date], “dd-mm”)

The following would be the result:

You can also replace the “dd-mm” to just “d-m” in order to see the single digits as it is.

You can make all sorts of different combinations using the previous formulas. For example, if you wanted to see only date and year, you could do that too by entering the formula below:

General Dates = FORMAT([Date], “dd-yyyy”)

All sorts of combinations can be made possible using power bi.

Not just this but you can also replace the hyphen (“-“) between the dates and years to a backslash (/).

You can do so by entering the following formula:

General Dates = FORMAT([Date], “dd/yyyy”)

You can also replace the backslash to a colon (“:”) by just replacing it. As always, you’re not limited to just backslashes, colons and hyphens but can also use semi-colons and more.