Calculating values by 30 days back in history returns different values

[Dillinger]

Dear Community,

I found a solution to my problem after couple of hours of struggling. However, the experience was quite confusing and disturbing and therefore I would like to see a professional's explanation regarding the matter.

Goal: sum values in [Value] that have a null in [ExecutionDate] and [DateOrigin] is older or as old as 30 days.

Problem: first three syntax's give wrong result when sum value is reconciled with Excel and SQL query.

Right sum value I was looking for is 861,180.88.

First syntax below returned wrong sum value 861,886.26:

FirstTry = 
CALCULATE( SUM( DataTable[Value] );
    TODAY() - DataTable[DateOrigin] <= 30;
    ISBLANK( DataTable[ExecutionDate] ) = TRUE()
)

I tried to check if the TODAY() somehow messes things up by using DATE() as today 22nd August 2019. Also second syntax returned wrong sum value 861,886.26:

SecondTry = 
CALCULATE( SUM( DataTable[Value] );
    DATE(2019;8;22) - DataTable[DateOrigin] <= 30;
    ISBLANK( DataTable[ExecutionDate] ) = TRUE()
)

Third try also wrong sum value 861,886.26:

ThirdTry= 
CALCULATE( SUM( DataTable[Value] );
    DATEDIFF( DataTable[DateOrigin]; DATE( 2019;8;22 ); DAY ) <= 30;
    ISBLANK( DataTable[ExecutionDate] ) = TRUE()
)

Fourth try and success with right sum value 861,180.88:

Success = 
CALCULATE( SUM( DataTable[Value] );
    DATESBETWEEN( DataTable[DateOrigin]; TODAY() - 30 ;TODAY());
    ISBLANK( DataTable[ExecutionDate] ) = TRUE()
)

SQL query returns also right sum value 861,180.88:

SELECT sum(DataTable.Value) FROM [DataTable]
where DataTable.ExecutionDate is null and DataTable.DateOrigin between '7-23-2019' and '8-22-2019'

In Excel, if I filter dates from 23rd July to 22nd August I get the same right sum value 861,180.88 too.

So is someone able to say where on earth is the error in first three syntaxs, please?

If I try to see what DATEDIFF returns, by all reasons it should work but it doesn't in third try. Below syntax returns me 30:

DATEDIFF = DATEDIFF(DATE(2019;7;23);TODAY();DAY)

Thanks in advance!

[v-diye-msft · Accepted solution]

Hi @Dillinger ，

Assuming today is 22nd, It seems the synatx you used in the first 3 ones define July 23th - today+1, but the success one define the July 23th - today. 

I created a sample as below:

Firstly I generate some data between 7/22 and 8/23, and the four syntaxes return the same result.

Then I add a record of 8/24 (the next day), the forth syntax is the correct answer,

So  my explanation is that in the first three answer, the further date are all meet the filter but do not meet your need, such as the first syntax, the today – Next day = -2 < 30 is right.

(because the today() means 0:00 of today, so the real today – today() = -1)

If you want to correct the first syntax, you can add a filter of date like the following.

FirstTry3_2 =

CALCULATE (

    SUM ( 'Table3'[Value] ),

    AND (

        TODAY () - 'Table3'[DateOrigin] <= 30,

        TODAY () - 'Table3'[DateOrigin] >= -1

    ),

    ISBLANK ( 'Table3'[ExecutionDate] ) = TRUE ()

Pbix attached.

[Dillinger]

@v-diye-msft Thank you dearly!