cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Unexpected 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] )
    ) + 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

 

7 REPLIES 7
Super User
Super User

Re: Unexpected behavior when filtering dates at the minute level

@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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: Unexpected behavior when filtering dates at the minute level

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

Super User
Super User

Re: Unexpected behavior when filtering dates at the minute level

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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: Unexpected behavior when filtering dates at the minute level

@Greg_Deckler

 

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

 

Super User
Super User

Re: Unexpected behavior when filtering dates at the minute level

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. 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: Unexpected behavior when filtering dates at the minute level

@Greg_Deckler

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.

 

Highlighted
marcorusso Member
Member

Re: Unexpected behavior when filtering dates at the minute level

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!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 281 members 3,149 guests
Please welcome our newest community members: