DATEDIFF DAX

[wemsomba]

Hello everyone. I want to get the difference in days between two or three columns from two different tables. I have "Name","firstcontact","Secondcontact","ThirdContact", and "FourthContact" as columns. I want to be able to to have two filters for the contacts so i can see the difference in days between each contacts. I have the same code uploaded in power bi as two different data sets. one is selected the other is compared. I unpivotted all 4 contact colums in power query to have them as Attribute column (which has 1st...4th contact as values of the column) and Value (which has dates as values of the column) also as fields. I have created two filters with the contact columns that i unpivotted. One will be Selected filter and the other Compared filter.

Now i want to have this outcome . I WANT TO BE ABLE TO COMPARE ANY CONTACT FROM SELECTED FILTER TO ANY FROM COMPARED FILTER. I have the DAX below but power bi not recognizing the values. please help. below is also the code.

DATE DIFF = DATEDIFF(Selected[Value], Compared[Value])

create table Selected(

Name varchar(30), FirstContact datetime,SecondContact datetime,ThirdContact datetime,FourthContact datetime)

insert into Selected (Name,FirstContact,SecondContact,ThirdContact,FourthContact)

VALUES

('James','1/1/2026 01:30:00','1/15/2026 01:30:00','1/17/2026 01:30:00','1/19/2026 01:30:00'),

('Anthony', '1/17/2026 01:30:00','1/19/2026 01:30:00','1/21/2026 01:30:00','1/22/2026 01:30:00'),

('Okito', '1/17/2026 01:02:00','1/16/2026 01:30:00','1/22/2026 01:30:00','1/31/2026 01:30:00')

SELECT *FROM Selected

DROP TABLE Selected

create table Compared(

Name varchar(30), FirstContact datetime,SecondContact datetime,ThirdContact datetime,FourthContact datetime)

insert into Compared (Name,FirstContact,SecondContact,ThirdContact,FourthContact)

VALUES

('James','1/1/2026 01:30:00','1/15/2026 01:30:00','1/17/2026 01:30:00','1/19/2026 01:30:00'),

('Anthony', '1/17/2026 01:30:00','1/19/2026 01:30:00','1/21/2026 01:30:00','1/22/2026 01:30:00'),

('Okito', '1/17/2026 01:02:00','1/16/2026 01:30:00','1/22/2026 01:30:00','1/31/2026 01:30:00')

SELECT *FROM Compared

DROP TABLE Compared

[v-nmadadi-msft]

Hi @wemsomba 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @wemsomba 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[lbendlin · Accepted solution]

Power BI does not think like Excel.  Unpivot your data  to these columns

Name

Contact

let
    Source = #table(null,{{ "James ", "1/1/2026 01:30:00 ", "1/15/2026 01:30:00 ", "1/17/2026 01:30:00 ", "1/19/2026 01:30:00 "},
{ "Anthony ",  "1/17/2026 01:30:00 ", "1/19/2026 01:30:00 ", "1/21/2026 01:30:00 ", "1/22/2026 01:30:00 "},
{ "Okito ",  "1/17/2026 01:02:00 ", "1/16/2026 01:30:00 ", "1/22/2026 01:30:00 ", "1/31/2026 01:30:00 "}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Contact"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Column1", "Contact"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column1", "Name"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Name", type text}, {"Contact", type datetime}})
in
    #"Changed Type"

That way the number of contacts for each name can be flexible.

Then you can run the standard math functions against your data, including min and max difference between values

To compare any two names you would use measures that can modify the filter context, or visual calculations.

Oftentimes a visual solution may be easier.

What is your expected outcome based on the sample data you provided?