Power bi Date Difference – 8 Different Examples

In this Power bi tutorial, we will discuss the Power bi date difference. And also we will discuss the below points:

• Power bi date difference between two columns
• Power bi date difference from today
• Power bi date difference measure
• Power bi date difference between two tables
• power bi date difference between two rows
• power bi date difference in months
• Power bi date difference in query editor
• power bi date difference between two in same column

Power bi date difference between two columns

Here we will see how to calculate the date difference between two date columns i.e. order date and ship date in power bi desktop.

• Open Power bi desktop and Load the data using get data.
• We will add calculated column which will show the difference between two columns.
• Click on the Modelling tab -> New column from the ribbon.
• Then write the DAX formula to find out the difference between the two date column.
• The DAX formual is Days between = DATEDIFF(Orders[Order Date],Orders[Ship Date],DAY)
• Or You can use this Dax formula also diff = 1.0*(Orders[Ship Date]-Orders[Order Date]), both give the same result.

• Next I have create the table visual and I have added order date field, ship date field, days between Calculated column and diff Calculated column.
• Here I have used both the calculated column to show you the result, both giving the same result, you can use any one.

This is how to calculate the difference between two columns in Power BI.

Read Power BI Slicer between two dates

Power bi date difference from today

Here we will see how to calculate the Power bi date difference from today in the power bi.

• To find the difference between the date from today, for this we will create calculated column.
• Go to modelling tab -> New column from the ribbon in power bi desktop.
• Then write the DAX measure:Diff = DATEDIFF(Table1[Date], TODAY(),DAY)

In the below screenshot, you can see the Power bi date difference from today.

This is an example of Power bi date difference from today.

Power bi date difference measure

Here we will see how to calculate power bi date difference measure in power bi.

• To calculate the power bi date difference using measure, For this we will create a measure.
• So click on the measure in the ribbon, then write the Dax measure is: No.of days = SUMX(Orders, DATEDIFF(Orders[Order Date],Orders[Ship Date],DAY))

In the below screenshot you can see the Power bi date difference using the measure.

Read Microsoft Power BI KPI Visual

Power bi date difference between two tables

Here we will see how to calculate the power bi date difference between two tables.

• Let’s say we have two tables one contact table and the Event table. In the contact table where we can find the date of birth of every person.
• And in the event table, you can see date of event occured and also it hold another field i.e. ID of the person from contact table, to show whom the event is relted.
• We will create a Calculated column which will hold the age of the person was when the event occured.
• Contact Table

• Event table

• To find the difference between two table, we have relationship between two table.
• So, make sure that contact and event table have cross filter relationship both.

• Now we will find the difference in the dates of the two tables, i.e., the age when the person is attending the event.
• Click on the modelling tab -> new column from the ribbon in power bi desktop.
• Then write the Dax formula : Age = DATEDIFF(RELATED(Contact[DOB].[Date]),Event[Date ].[Date],YEAR)

Now I have created a table visualization, where I have added name, Date(event), Event, and age( calculated column).

Read Power bi measure subtract + 7 useful examples

Power bi date difference between two rows

Here we will see how to find the date difference between two rows in power bi desktop.

I have created sample data to find out the date difference between two rows.

For example, if the difference between the two-row 100-100=0( from 31/Jan/2017 to 28/Feb/2017)

Let’s see how to find the difference between two rows.

• For this click on the modelling tab-> new column from the ribbon in power bi desktop.
• Then write the Dax formula:

diff row = [MRR]-LOOKUPVALUE(Data[MRR],Data[Account Name],[Account Name],Data[Report Date],CALCULATE(Max(Data[Report Date]),FILTER(Data,Data[Account Name]=EARLIER(Data[Account Name])&&Data[Report Date]<EARLIER(Data[Report Date])&&Data[MRR]<>BLANK())))

In the below screenshot you can see the power bi difference between the two rows.

Read Power bi slicer contains

Power bi date difference in months

Here we will see how to calculate date difference between the two months in power bi.

• We will take the two date column i.e order date and ship date to find the difference in months.
• Click on the modelling tab-> New column from the ribbon in power bi desktop.
• Then write the Dax formula:No.of month = DATEDIFF(Orders[Order Date],Orders[Ship Date],MONTH)

In the below screenshot you can see the power bi date difference in month.

Read Power BI Measure IF with Examples

Power bi date difference in query editor

Here we will see how to calculate the date difference in the query editor in the power bi desktop.

To calculate the difference between the two dates we will use the power query(M code), not Dax.

• In power bi desktop click on the transform data to open power query editor.
• Once power query editor open, Click on the Add column tab.
• Click on the custom column, to add M code.

• Then custom column window will open, then write the new column name.
• Under custom column formula, write the formula i.e. Duration.Days([Ship Date] – [Order Date])
• Make sure that there should not be any syntax error. Click on Ok.

Once you can click on Ok, you can see the custom column in the power query. Click on Close and apply to use the custom column.

Read Power bi slicer checkbox

Power bi date difference between two in the same column

Here we will see the power bi date difference between the two in the same column in power bi.

• We will create the calculated column which generates rhe number of days between two dates.
• Whereas the two dates are in same column, and i want to calcualte the number of days two chronolgy adjacent dates when there are multiple dates values, For example the difference between date1 and date2 , date2 and date3 and so on.

For this, we will create a calculated column in the power bi desktop.

• In power bi desktop, click on the modelling tab then click on new column to create a calculated column.
• Then write the Dax formula:

Column = 
VAR temp =
    TOPN (
        1,
        FILTER (
            'Customer visit',
            'Customer visit'[Customer id] = EARLIER ( 'Customer visit'[Customer id] )
                && 'Customer visit'[DateOfVisit] < EARLIER ( 'Customer visit'[DateOfVisit] )
        ),
        [DateOfVisit], DESC
    )
RETURN
    DATEDIFF ( MINX ( temp, [DateOfVisit] ), 'Customer visit'[DateOfVisit], DAY )

In the below screenshot you can see the Power bi date difference between the two in the same column.

You may like the following Power Bi tutorials:

• Power bi date format
• Power bi group by date range
• Power bi date filter
• Power BI Date Hierarchy
• Power bi create a date table
• How to create a measure in Power BI
• Power BI Date Function with Examples

In this power bi tutorial, we discussed power bi date difference. And also we discussed the below points:

• Power bi date difference between two columns
• Power bi date difference from today
• Power bi date difference measure
• Power bi date difference between two tables
• power bi date difference between two rows
• power bi date difference in months
• Power bi date difference in query editor
• power bi date difference between two in same column