Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Day difference in some rows wrong

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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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))
Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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
Not applicable

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
Community Champion
Community Champion

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


Regards,
Nandu Krishna

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.