Forum Discussion
Community ChampionUnexpected behavior when filtering dates at the minute level
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] )
) + 0This 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
7 Replies
- Greg_Deckler
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.
- AlB
Hi Greg_Deckler
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
- Greg_Deckler
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.
