How to find the number of days between rows

[Joel_sony]

Hey guys! Can someone help me out how to create a new column which calculates the difference of days between each row similiar to the days_difference int this table?

[DataNinja777 · Accepted solution]

Hi @Joel_sony, 

You can produce your required output by writing a calculated column using dax as follows:

days_difference dax = 
VAR CurrentIndex = 'Table'[Index]
VAR CurrentDate = 'Table'[created at]

// Find the previous date for the same ID using the previous index
VAR PreviousDate = 
    CALCULATE(
        MAX('Table'[created at]),
        FILTER(
            'Table',
            'Table'[Index] = CurrentIndex - 1
        )
    )

RETURN
    IF(
        ISBLANK(PreviousDate), 
        0, 
        DATEDIFF(PreviousDate, CurrentDate, DAY)
    )

The above dax will produce the last column as shown below:

To solve this in Power BI, start by adding an index column in Power Query. Power Pivot DAX doesn’t retain the inherent row order during calculations, so adding an index column helps establish a fixed order for accurate calculations.

I have attached an example pbix file for your reference.

Best regards,

[PhilipTreacy · Accepted solution]

@Joel_sony 

Use Power Query to subtract the dates from each other.  First you need to add an Index column so you can refer to the previous row but then you just need to add a Custom Column with this

= try Duration.Days(Date.From([created_at]) - Date.From(#"Added Index"[created_at]{[Index]-1})) otherwise 0

and delete the Index column as it's no longer needed to give this

This is the entire query which you can also download from this example PBIX file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc/BCgIhEIDhVwnPK8you9q+iixh7RBCWbhW9PbpJcpswYMwH+OvtQwl69ghkks055sANByBo9igGsGMSrKps0yIPFySixXT3wwBi7vtzz695ZaD5ChrWTa64E7PJe0i3T09VnnpvFKYfTiuOpWnkS6Zrr7f97pZmr+lamn+l/7yAYZGqeESKoe6VZrP58LpBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, status = _t, created_at = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"status", type text}, {"created_at", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "days_difference", each try Duration.Days(Date.From([created_at]) - Date.From(#"Added Index"[created_at]{[Index]-1})) otherwise 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Regards

Phil

[ThxAlot · Accepted solution]

Easy enough,

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[id]<EARLIER(Data[id])))=0,0,int(Data[created_at]-CALCULATE(MAX(Data[created_at]),FILTER(Data,Data[id]<EARLIER(Data[id])&&Data[created_at]<=EARLIER(Data[created_at])))))

Hope this helps.

[Joel_sony]

Thanks alot! @Ashish_Mathur 

[Ashish_Mathur]

You are welcome.

[ThxAlot · Accepted solution]

Easy enough,

[Joel_sony]

Thankyou @ThxAlot 

[PhilipTreacy · Accepted solution]

@Joel_sony 

Use Power Query to subtract the dates from each other.  First you need to add an Index column so you can refer to the previous row but then you just need to add a Custom Column with this

= try Duration.Days(Date.From([created_at]) - Date.From(#"Added Index"[created_at]{[Index]-1})) otherwise 0

and delete the Index column as it's no longer needed to give this

This is the entire query which you can also download from this example PBIX file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc/BCgIhEIDhVwnPK8you9q+iixh7RBCWbhW9PbpJcpswYMwH+OvtQwl69ghkks055sANByBo9igGsGMSrKps0yIPFySixXT3wwBi7vtzz695ZaD5ChrWTa64E7PJe0i3T09VnnpvFKYfTiuOpWnkS6Zrr7f97pZmr+lamn+l/7yAYZGqeESKoe6VZrP58LpBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, status = _t, created_at = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"status", type text}, {"created_at", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "days_difference", each try Duration.Days(Date.From([created_at]) - Date.From(#"Added Index"[created_at]{[Index]-1})) otherwise 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Regards

Phil

[Joel_sony]

Thankyou @PhilipTreacy !

[DataNinja777 · Accepted solution]

Hi @Joel_sony, 

You can produce your required output by writing a calculated column using dax as follows:

days_difference dax = 
VAR CurrentIndex = 'Table'[Index]
VAR CurrentDate = 'Table'[created at]

// Find the previous date for the same ID using the previous index
VAR PreviousDate = 
    CALCULATE(
        MAX('Table'[created at]),
        FILTER(
            'Table',
            'Table'[Index] = CurrentIndex - 1
        )
    )

RETURN
    IF(
        ISBLANK(PreviousDate), 
        0, 
        DATEDIFF(PreviousDate, CurrentDate, DAY)
    )

The above dax will produce the last column as shown below:

To solve this in Power BI, start by adding an index column in Power Query. Power Pivot DAX doesn’t retain the inherent row order during calculations, so adding an index column helps establish a fixed order for accurate calculations.

I have attached an example pbix file for your reference.

Best regards,

[Joel_sony]

Thankyou @DataNinja777 

[muhammad_786_1]

Hi @Joel_sony 

You can try this DAX to get your desired outcome:

days_difference = 
VAR CurrentDate = 'Table'[created_at]

VAR PreviousDate = 
    CALCULATE(
        MAX('Table'[created_at]),
        FILTER(
            'Table',
            'Table'[created_at] <= EARLIER('Table'[created_at]) &&
            'Table'[id] < EARLIER('Table'[id])
        )
    )

RETURN
IF(
    ISBLANK(PreviousDate),
    0,
    IF(CurrentDate = PreviousDate, 
        0, 
        DATEDIFF(PreviousDate, CurrentDate, DAY)
    )
)

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[Joel_sony]

Thankyou @muhammad_786_1 ! Great help!

[Hakuna_matata]

Hi @Joel_sony ,
We can achieve this by the below Dax creating a calculated column
Days_Difference =
VAR CurrentDate = 'Table'[Date]
VAR PreviousDate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
'Table'[Date] < CurrentDate
)
)
RETURN
IF(ISBLANK(PreviousDate), BLANK(), DATEDIFF(PreviousDate, CurrentDate, DAY))

Please mark this as a solution if this help. Hope it helps!

[Joel_sony]

Thankyou @Hakuna_matata !