Power BI Measure Date + Examples

In this Power BI Tutorial, we will discuss the Power BI Measure Date. Also, we will consider below topic:

• Power BI Measure Date difference
• Power BI Measure Date format
• Power BI Measure date between(DATESBETWEEN())
• Power BI measure date filter
• Power BI measure date hierarchy
• Power BI Measure Date today
• Power BI Measure Date difference from today

Power BI Measure Date

In Power BI, when we want to create or format a measure for the date, we need an expression that returning a data type as date/time format.

For example, here we will create a simple measure that will show the current date & time in date/time format.

Measure = NOW()

To execute these above subtopics, here we are going to use sample data (excel file). Not only Excel files, but also we can import data from SharePoint list, txt file, My SQL database, CSV file, etc.

Read Power bi sort by measure

Power BI Measure Date difference

Here we will see how to calculate the difference of days in between two dates using a Power BI Measure.

Let’s take an example of our sample data. We have 2 date columns as Order Date and Ship Date. Using these columns we will create a table:

Now we will create a measure, that will calculate the total no. of days between the Order date and Ship date.

Total_No._Of_Days = SUMX('Orders', DATEDIFF('Orders'[Order Date].[Day], 'Orders'[Ship Date].[Day], DAY))  

Read Power BI Measure Filter

Power BI Measure Date format

Here we will see how to format the date column by using a DAX formula in a Power BI Measure.

For example, we are going to use those date columns and format these columns from text to MM/DD/YYYY format using a Measure.

Order_date_format = FORMAT(MIN(Orders[Order Date]),"M/dd/yyyy")

Similarly, we can format the Ship date column to dd/mm/yyyy format using DAX Measure.

Also, we can format the date column without using the DAX expression. For this:

go to Power BI Data page > select the date column(order column)

This is how we can format the Date column without using Measure and using Measure in Power BI.

Read Power bi measure divide + 8 examples

Power BI Measure date between (DATESBETWEEN())

In Power BI, the DATESBETWEEN() function is used to return a table that contains a date column that begins with a specified start date to an end date.

Now we will see how to calculate the sum in between specific start date to end date by using this function in a measure.

For example, we have a table contains the Product’s sale and ship date(from sample data).

Now we will create a measure that will calculate the total sale between 1/1/2015 to 1/10/15.

TotalSold = CALCULATE (SUM (Orders[Sales]), DATESBETWEEN(Orders[Ship Date], DATE (2015,1,2), DATE (2015,1,10)))

To show this amount, we are going to use a card visual.

The above chart showing the sum of total sale between those specified dates.

Read Power bi measure switch statement with examples

Power BI Measure Date today

In Power BI, there is a date function as Today(). It returns the current date in a DateTime format.

To build the current date here we will create a measure. And the DAX expression is:

Today = TODAY()

We will use a card chart to show this measure in it:

By applying this simple DAX expression we can reveal today’s date.

Read Countif function in Power BI Measure + 10 Useful Examples

Power BI Measure Date difference from Today

Here we will see how to calculate the difference of days to a specific date from today.

For this here we will create a simple date table having two date columns. One is some random dates and another is today’s date.

Now we will create a measure that will calculate the days from today to the previous date.

total_days = SUMX('Date Table', DATEDIFF('Date Table'[Previous Date], TODAY(), DAY)) 

From the above table, we can see by using this measure will calculate the date difference from today.

Read Microsoft Power BI Combo Chart

Power BI measure date hierarchy

Follow this link to get an idea to create a Date hierarchy.

Power BI measure date filter

Here we will see how to create a measure that filters the data in Power BI.

For this, we have created a table having a date column (including today’s date i.e. 27th Aug 2021) and a numeric column.

Now we will create a measure that will filter and calculate the sum of the total sale, that were sold before today.

measure_filter = CALCULATE(SUM('Table'[Sale]), FILTER('Table', 'Table'[Date] < TODAY()))

As the current date is 8/27/2021, so this measure filtered all the dates (before today) and returned the SUM of the total sale. We can see the difference and comparison between the previous table and the filtered table.

You may like the following Power BI tutorials:

• Power BI Matrix
• Power BI Slicer – How to use with examples
• How to use Power bi maps
• Power BI Filter vs Slicer
• How to use Microsoft Power BI Scatter Chart
• Power Bi key influencers
• Power BI Measure multiply with examples
• Power Bi Relationship Functions

Conclusion

From this Power BI Measure tutorial, we learned about:

• How to calculate the difference of dates using Power BI Measure?
• How to do format Date using Power BI Measure?
• How to works with dates between (DATESBETWEEN() function) in Power BI Measure?
• How to do filter date by Power BI measure?
• How to show current date using Power BI Measure?
• How to calculate Power BI Measure Date difference from today?