Incorrect measure value with sum of filter

[Camseg]

Hi,

I have a measure returning an incorrect result in some cases and I can't figure out why. Please help!

(In the following example, i have removed all the non-problematic data)

The goal is to show amounts owed that were 60 to 90 days old at a date specified by the user. To do that, I use two measure. 

[Slicer] returns the last date on the calendar table based on the value selected by the user on the slicer. 

Slicer = MAXX(ALLSELECTED('Calendar'[Date]),[Date])

[60-90 days] sums all the Montant_Net (net amount in french sorry) if the Date_Du is 61 and 90 day old based on report date (the date selected by the user and returned by the Slicer Measure).

60-90 days = CALCULATE(SUM(GL[Montant_Net]), FILTER('GL', DATEDIFF(GL[Date_Du],[Slicer],DAY)>=61 && DATEDIFF(GL[Date_Du],[Slicer],DAY)<=90))

And this is the simplified data where Daysbetween_Slicer_DateDu is a test measure = DATEDIFF(GL[Date_Du],[Slicer],DAY).

Here is an example PBIX file: https://drive.google.com/file/d/1j4i6j7AZnXtm_S_x91dd2uorg2cxaxjH/view?usp=drive_link 

In that example, 2023-08-31 is selected by the user on the slicer and the the expected result of [60-90 days] would be 0 since no entries were between 61 and 90 days old. Yet the measure returns 1 724 625$. Also, I can't explain why but if I edit the measure to <=87 or less, it returns 0. 

I have spent a few hours on this issue and tried many things including:

- Using a calculated table instead of a measure

- Changing the type of all my dates to date or date time

- Using AND() instead of && or using various other syntax instead of DATEDIFF

I can't understand what's causing this. Any help will be greatly appreciated. 

[Greg_Deckler]

@Camseg This works:

60-90 days NC = 
    VAR __Slicer = MAX('Calendar'[Date])
    VAR __Table = FILTER('GL', DATEDIFF(GL[Date_Du],__Slicer,DAY)>=61 && DATEDIFF(GL[Date_Du],__Slicer,DAY)<=90)
    VAR __Result = SUMX(__Table, [Montant_Net])
RETURN
    __Result

[Camseg]

Hi @Greg_Deckler , thank you for your reply.

It does return 0 but when I add more data (that was hidden for the example) it always return 0 no matter the data. 

As an example, here, GL_ID 441977 and 444399 should be included as they are 71 day old.

Same example as before but with more data:

https://drive.google.com/file/d/1j4i6j7AZnXtm_S_x91dd2uorg2cxaxjH/view?usp=drive_link 

[FreemanZ]

hi @Camseg ,

try like:

60-90 days = CALCULATE(

                SUM(GL[Montant_Net]),

                FILTER(

                    ALL('GL'),

                    DATEDIFF(GL[Date_Du],[Slicer],DAY)>=61 && DATEDIFF(GL[Date_Du],[Slicer],DAY)<=90

                    ))

[Camseg]

Hi! Thank you for your quick reply.

Result is unchanged with the ALL(). I failed to mention it but I tried various combination of ALL() ALLEXCEPT() ALLSELECTED().