Why does the result show abnormal after I using the DATEDIFF function

[daniel_Chen2023]

Hi All,

I created a new column and used the DATEDIFF function to calculate the number of days. But I found that some results display correctly. And others display incorrectly. I have confirmed that the data format is correct. Why does cause this problem? Thanks.

[amitchandak]

@daniel_Chen2023 , In the visual, not all rows will be shown, data will be grouped by the two dates. Can you add some ID columns in visual and check or Use Average aggregation for the date diff column in visual

[daniel_Chen2023]

Hi @amitchandak ,

Thanks for your help. Because I am the beginner of the Power BI.I am not very familiar with Power BI.

I have used the average funciton for the Datediff data. But the data is still incorrect.

At the same time,  I do not konw how to add the ID column by DAX function after using datediff function. Should I save the report and then opne with the Power Query ?

By the way, I also want to konw which two dates are diff dates grouped based on?

[amitchandak]

@daniel_Chen2023 , If you have taken a date diff in power query or dax as new column. and then try to put that in visual along with some columns. The data will be grouped by those columns.

Assume I have Date1, Date2 (use date , do not use hierarchy like you are doing) and sum/avg od date diff.

That means data will be group by date,1 date2, and that the level of data.

If you need to display line level data , add index column in power query and add that as not summarized

Power Query- Index Column: https://youtu.be/NS4esnCDqVw

[daniel_Chen2023]

Hi @amitchandak ,

Thanks for your support. I am sorry to reply you too late. Because I was on public holiday before. 

I attempt to follow your way. Now I find the data is correct.

Thanks