Day difference in some rows wrong

[Anonymous]

Hello experts,
I have never seen this before. I created some day diff calculation and noticed in about 20 rows (among thousands) the value is wrong. I tried both calculated column and measure. Both showing same.
Initial day and last day in different table , connected by ID2. For measure calculation, I used userrelationship to use connection in between id1.
Any idea why I am seeing wrong value for row 3rd?
Id1/ id2. /. Initial day/. Last day/. Day diff
001. /39493. /Mar13,2019. /Sep20,2018. /-174
002. /39578. /Nov 4,2018. /Sep 20,2018. /-410
003. /39000/ Mar 13, 2019./ Sep 20, 2018 /36508

[amitchandak]

In case you are using a measure. Or dates across tables, You need to take care of context. Check my blog on this :

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

Max Last_day= max(Table[Last_day])
Min Initial_day = MIN(Table[Initial_day])
Date Diff = AVERAGEX(VALUES(Table[Id2]) ,datediff([Initial_day],[Max Last_day],DAY))
Date Diff 2 = AVERAGEX(SUMMARIZE(Table,Table[ID2].Table[Id1] ,"_min",[Min Initial_da],"_max",[Max Last_day]),datediff([_min],[_max],DAY))

[Anonymous]

Thanks Amit.i tried exactly your way. Still same.
To elaborate more
Mine is import mode. In table 1 I have initial date and they are unique for each ID2. In table 2, I have multiple last dates for each ID2. I created a column in table2 with lookup value to put the initial date for each ID2. Tried to use Dax with max from one of your other post, it is not working as getting values for even rows where there is no ID2 listed. Table1 and 2 are connected with id1, so created a day diff measure using datediff Dax.
Not working. Could not create cal column as showing circular dependency.
Not sure why only for few rows it is showing

[Anonymous · Accepted solution]

Update,
Found the problem. The rows where day diff was coming wrong, data in database were wrong like 01032013 became 01033013. As data was formatted as 03-Jan-13 in both database and PBI, it went unnoticed.
Thanks to all for helping me.

[nandukrishnavs]

@Anonymous  Can you provide a sample dataset with rows having issues?