cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
urspalani Regular Visitor
Regular Visitor

Date difference - When two dates in different rows and columns

Hi,

 

I want to calculate the date difference ,

ED - SD where SD and ED are different Columns and different rows,

 

Capture.PNGAnyone having idea,

 

Thanks,

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Date difference - When two dates in different rows and columns

@urspalani,

 

Add an index column in Query Editor and a calculated column in DAX.

Column =
VAR ed =
    IF (
        Table1[Type] = "Arr",
        LOOKUPVALUE (
            Table1[ED],
            Table1[Index], Table1[Index] + 1,
            Table1[Type], "Dep"
        )
    )
RETURN
    ed
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Phil_Seamark Super Contributor
Super Contributor

Re: Date difference - When two dates in different rows and columns

Hi @urspalani

 

Do you have any other columns to link the top 2 rows together (I assumed they are linked), but not to the bottom two?

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

urspalani Regular Visitor
Regular Visitor

Re: Date difference - When two dates in different rows and columns

They are linked based on Arr & Dep

Phil_Seamark Super Contributor
Super Contributor

Re: Date difference - When two dates in different rows and columns

So is all the data sequential?  Whats to stop linking the top row from the bottom row?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

urspalani Regular Visitor
Regular Visitor

Re: Date difference - When two dates in different rows and columns

This is just a example,

In real time i will get different Report type that is Arr,Dep,ONH,BOSP,EOSP

I have to find the difference always for Arr & Dep ,it will appear in different line not next two each other ,Based on Report type i have find the difference in dates,

Community Support Team
Community Support Team

Re: Date difference - When two dates in different rows and columns

@urspalani,

 

Add an index column in Query Editor and a calculated column in DAX.

Column =
VAR ed =
    IF (
        Table1[Type] = "Arr",
        LOOKUPVALUE (
            Table1[ED],
            Table1[Index], Table1[Index] + 1,
            Table1[Type], "Dep"
        )
    )
RETURN
    ed
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.