- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Unexpected behavior when filtering dates at the minute level

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2019 02:27 PM - last edited Friday

Hi there,

I've come across some unexpected behavior. I'd like to know if you can reproduce it and if so what might be the reason for it. Here is the file.

We have a very simplified scenario: a one-column table with a date-times with minute granularity. No gaps.

ExpDate |

04/02/2019 11:34:00 |

04/02/2019 11:35:00 |

04/02/2019 11:36:00 |

04/02/2019 11:37:00 |

and so on ... |

Now we add a calculated column as follows:

Column1 = VAR _OneMinute = 1 / 24 / 60 RETURN CALCULATE ( COUNT ( 'DataTable'[ExpDate] ); 'DataTable'[ExpDate] >= EARLIER ( 'DataTable'[ExpDate] ) - ( 15 * _OneMinute ); 'DataTable'[ExpDate] < EARLIER ( 'DataTable'[ExpDate] ) ) + 0

This rather useless code returns, for each row, the number of rows that exist between the minute previous to the current row and 15 minutes before. For instance, if we are at 12:45, it will count the rows with times between 12:30 and 12:44 (both included). Since we have no gaps and one-minute steps in the dates-times, the result should always be 15 (except on the 14 rows with earliest date-times). No big mystery so far.

The problem is that I get a mix of 15s and 14s. I'm not sure where the 14s come from. I suspect it has to do with rounding, since I am treating the dates as numbers but still, it should work without problems. **Is there a reason for this behavior?** **Anybody know what is going on?**

If we create another column using a different yet theoretically equivalent approach, we get all 15s as expected (except on the earliest rows as seen before):

Column2 = CALCULATE ( COUNT ( DataTable2[ExpDate] ); DATEDIFF ( DataTable2[ExpDate]; EARLIER ( DataTable2[ExpDate] ); MINUTE ) >= 1; DATEDIFF ( DataTable2[ExpDate]; EARLIER ( DataTable2[ExpDate] ); MINUTE ) <= 15 ) + 0

Many thanks

## Re: Unexpected behavior when filtering dates at the minute level

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2019 03:30 PM - edited 02-10-2019 03:30 PM

@AlB - You indeed have some sort of rounding error going on. You can see it if you create the following three four columns:

Column = //Calculates the datetime 15 before VAR _OneMinute = 1/24/60 RETURN 'DataTable'[ExpDate] - ( 15 * _OneMinute ) Column2 = //I created this because when I added the column above it caused your column to fail and return all 0's VAR _OneMinute = 1 / 24 / 60 RETURN COUNTROWS(FILTER(ALL('DataTable'),'DataTable'[ExpDate] >= EARLIER('DataTable'[ExpDate]) - ( 15 * _OneMinute ) && 'DataTable'[ExpDate] < EARLIER ( 'DataTable'[ExpDate] ))) + 0 Column 2 = //Converts the 15 minutes before datetime column to a decimal value (which is how it is stored) [Column] * 1. Column 3 = //Converts the current row ExpDate to decimal (which is how it is stored) [ExpDate] * 1.

If you compare a row in which Colum2 returns a 14, you will that "Column 2" number is slightly higher than the corresponding "Column 3" from 15 minutes ago.

Attaching edited file.

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

## Re: Unexpected behavior when filtering dates at the minute level

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 04:54 AM - last edited Friday

Thanks very much for your reply.

Could you please point to the exact location of a couple of examples where you see that small difference in the numbers? I have checked your file and could not find any. In fact I copied the data with the maximum number of decimals available (15) into an excel sheet and checked for differences between Column2 and Column3 15 mins prior and all I get is zeros (even with scientific notation).

Many thanks

## Re: Unexpected behavior when filtering dates at the minute level

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 05:30 AM

Sorry, too many numbers! I guess that pattern I see is that the 14's occur on the infinitely repeating numbers, where they end in things like 3333333 or 66666667 or 11111111, things like that. I wonder if that is causing some kind of issue where, let's say one precision is to 10 digits and the other to 5 so the in that situation one is larger than the other when it should really be equal.

@marcorusso loves nitty, gritty internal DAX issues, perhaps he has a take on it.

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

## Re: Unexpected behavior when filtering dates at the minute level

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 06:36 AM

Yeah, it must be something like that. In fact,

1/ 24/ 60 = 0.000694444444444...

with an infinitely repeating 4

If we use

_OneMinute = 0.0006945

instead, it seems to work fine.

I wonder in any case if it's then not reliable to work with dates as numbers in DAX?

Cheers

## Re: Unexpected behavior when filtering dates at the minute level

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 07:37 AM

Well, internally they are stored as decimal numbers. The integer portion is the number of days since, I think 12/30/1899 and the decimial portion is the time component. So, they are stored that way. I think that the real issue is what you mentioned with regards to 1/2/4/60 resulting in a repeating decimal.

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

## Re: Unexpected behavior when filtering dates at the minute level

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 07:47 AM - edited 02-11-2019 07:47 AM

Right. A decimal number in DAX is always stored as a double-precision floating point value, so I believe there should be no issue dealing with 1/24/60.

Let's see what @marcorusso says.

## Re: Unexpected behavior when filtering dates at the minute level

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 03:59 PM

It could be a rounding issue, definitely. Decimals have this problem. You can:

- Use ROUND to limit the decimal points to analyze

- or use CURRENCY( ... ) to convert the two terms to compare to a fixed decimal number

I see <= / > in one formula, and <= / >= in another - not sure whether this is relevant (I did not studied the code too much) but also pay attention to that.

I hope it helps!