Problems with Running total on DateTime2

[AudriusZ]

Hi,

I try to create a running total on SqlServer (Direct Query), by the Timestamp column (DateTime2).

RuningTotal = CALCULATE (
	SUM('Changes'[Delta]),
	FILTER(
		ALLSELECTED('Changes'[Timestamp]),
		'Changes'[Timestamp] <= MAX('Changes'[Timestamp])
    )
)

Filter comparison skips all values when time field contains fractions to nanoseconds. Same with ISONORAFTER.

If I change data in DB to not contain fractions, measure works fine.

Is this somehow by design?

thanks

[AudriusZ]

I think I've got it why. 

Direct Query executes this in several steps. First, it selects the distinct values for the axis column, then selects the sum, putting the axis values into sql 'WHERE IN()' condition; that way '<=' in DAX transforms into '=' in SQL.

Those values, while travelling SQL->PBI->SQL encounter conversions causing them to lose precision, thus changing. Finally, the changed values fail '=' comparison and the rows are skipped.

This amounts into somewhat lose interpretation of DAX by Direct Query, but given the complexities of it, can we ask for more?

Lessons learnt:

• I need to reduce and keep under control the granularity of the axis column
• I need to use datatypes and values that guarantee lossless value conversion

thanks

[v-yulgu-msft]

Hi @AudriusZ ,

Glad to hear that you have found the cause by yourself and thanks for knowledge sharing.

Regards,

Yuliana Gu