topic Incorrect measure value with sum of filter in DAX Commands and Tips

Incorrect measure value with sum of filter

Camseg — Thu, 26 Oct 2023 14:40:30 GMT

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.

Re: Incorrect measure value with sum of filter

FreemanZ — Thu, 26 Oct 2023 14:16:26 GMT

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

))

Re: Incorrect measure value with sum of filter

Camseg — Thu, 26 Oct 2023 14:28:48 GMT

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().

Re: Incorrect measure value with sum of filter

Greg_Deckler — Thu, 26 Oct 2023 14:45:34 GMT

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

Re: Incorrect measure value with sum of filter

Camseg — Thu, 26 Oct 2023 15:03:11 GMT

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