Difference between 2 columns in power bi

[Bakhtawar]

I am trying to take the difference between two columns. Below is an expression in qlik sense that I would like to translate into DAX

LPO_DATE - (CLM_INTM_DT) +1

I tried this in power bi DAX but I don't think it is correct:

V_TurnAround_Time = CALCULATE ( SUM ( LPO_excel[LPO_DATE] ) ) - CALCULATE ( SUM ( Claims_excel[CLM_INTM_DT] ) ) + 1

this is link model. dropbox.com/s/52j0s8rn0ze0mci/Claims_test_model.pbix?dl=0 .. in this please check variables table and in that check v_TurnAround_Time measure

[Tahreem24]

You need to create relationship between both the table (LPO_Excel, Claims_Excel) and then create measure or column that would work on 'LPO_Date' - 'CLM_INTM_DT'.

[Bakhtawar]

relationship already developed.. have you checked the link ?

[Tahreem24]

If have seen it so there is an Warning while creating a Many-to-Many relarionship. So make the key values unique and atleast single match in both the key column.

(See below screen shot of your file)

[Bakhtawar]

yes i know and this is the only key which should be link .. if i get any other realtionship then same error occured.. 

[Tahreem24]

Error occured due to below reason most probably:

1) Check whether the boht the key column dont have any null values.

2) Alteast one common value should be present in both the key column.

[Tahreem24]

From your data it is clearly shows that there NULL entry in key column (LPO_Key) in LPO_Excel table(Refer screen shot below) . So clear out this null values and then create one-to-many relation.

[Bakhtawar]

yes i checked and also i checked from original source . .date is fine.. i just need dax command how should i get days from two dates thats it 

[Tahreem24]

As your both the dates are coming from two different table. So in order to find the difference between these two date you'll have to create first relationship and then create measure to subtract from both the date value.

I have created some dummy tables that matches with your requirement and it gives truly expected value. Please see the step wise details in below screen shot:

If I did answer your question,So please this as a solution.

[Bakhtawar]

hello i want difference in numbes.. not in date.. and is this possible to first convert dates into number then minus both dates ? 

[Tahreem24]

Number in terms of Day,month,or Year?

IF you want differences in number od Day, Month,Year etc. so this can be achieved by  DATEDIFF or DATEDIFF DAX function.

Syntax:

1) DATEDIFF(Date1,Date2,Interval)   

2)DATEDIFF(Date1,Date2,Interval)

Here Interval can be Day,month,Quater,year,Minute etc.

Please mark this answer as a solution so other will get help from this.

[Bakhtawar]

hello i already tried that DATEDIFF function but when i tried eg .

DATEDIFF( here then i am trying to call date field CLM_INT_DT ) then this field did not appear