Power BI DateDiff seems to return incorrect number of days

1
votes

I'm using DAX DateDiff in Power BI to calculate the number of days between two dates, like this:

DaysDiff = DATEDIFF('MyTable'[Sales Order Date],
                    'MyTable'[Paid Date],DAY)

The formula is however returning some odd looking results, such as:

enter image description here

I would expect to see the actual number of days between the dates. For example, the number of days between 3/31/2017 and 12/4/2017 should be 248.

Both source columns are formatted as dates, and appear in the actual data as shown here.

How should the difference be calculated? I also tried a different formulation, which returns the same result:

DayDiff = 1.* ('MyTable'[Paid Date]-'MyTable'[Sales Order Date])
1
powerbidaxdatediff

1 Answers

4
votes

From the hint of the DaysDiff, my guess is that you have multiple records with same Sales Order Date and Paid Date, and Power BI has aggregated (default Sum) the results of all to one number.

If you change the summarization to either Average/Minimum/Maximum it should work fine.

result