Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlB
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
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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

 

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.