Date field condition is not working as expected for past dates

[Aman991]

Hey there,

I got into this issue where I noticed that the data that is fetched in slice using the row filter condition is not what it is supposed to be. Condition is working fine for recent dates but when we talk about dates from the year like 1960's, logic seems to be broken and not working as expected.


 

[AleksiAlkio]

May I ask what is your formula in the slice?

[Aman991]

Hi Aleksi,
this was the filter condition, I'm using.

AND(
or(
If([EventStatusLookupId]="Not Submitted", True,False),
If([EventStatusLookupId]="Nicht eingereicht", True,False)
),

Contains(select(swuser[Email], [Station name]= [Station Id]), useremail()),
IF(ISBLANK(ANY(Past event filter[Event Type])),TRUE,[EventTypeLookupId]=ANY(Past event filter[Event Type])),

IF(
AND(
ISNOTBLANK(Past event filter[Event Type]),
ISNOTBLANK(Past event filter[From]),
ISNOTBLANK(Past event filter[To])
),
SWITCH(
ANY(Past event filter[Event Type]),
in(Past event filter[Event Type], {"SpüMo Totvögel (Sondererfassung Totvögel)", "SpüMo Totvögel"}), AND(
LOOKUP([_THISROW].[Name], "MONITORING", "Name", "Conducted Date") >= ANY(Past event filter[From]),
LOOKUP([_THISROW].[Name], "MONITORING", "Name", "Conducted Date") <= ANY(Past event filter[To])
),

"SpüMo Müll", AND(
LOOKUP([_THISROW].[Name], "table name", "Event ID", "Conducted Date") >= ANY(Past event filter[From]),
LOOKUP([_THISROW].[Name], "table name", "Event ID", "Conducted Date") <= ANY(Past event filter[To])
),
"Wattkartierung", AND(
LOOKUP([_THISROW].[Name], "WMONITORING", "Event ID", "Conducted Start Date") >= ANY(Past event filter[From]),
LOOKUP([_THISROW].[Name], "WMONITORING", "Event ID", "Conducted Start Date") <= ANY(Past event filter[To])
),

"Gebietskontrolle", And(
LOOKUP([_THISROW].[Name], "Area Control", "Event ID", "Conducted Date") >= ANY(Past event filter[From]),
LOOKUP([_THISROW].[Name], "Area Control", "Event ID", "Conducted Date") <= ANY(Past event filter[To])
),

LOOKUP([_THISROW].[Name], "Monitoring", "Name", "Conducted Date") >= ANY(Past event filter[From])
),
IF(
ISNOTBLANK(Past event filter[Event Type]),
[EventTypeLookupId]=ANY(Past event filter[Event Type]),
IF(
AND(
ISNOTBLANK(Past event filter[From]),
ISNOTBLANK(Past event filter[To])
),OR(
AND(
LOOKUP([_THISROW].[Name], "MONITORING", "Name", "Conducted Date") >= ANY(Past event filter[From]),
LOOKUP([_THISROW].[Name], "MONITORING", "Name", "Conducted Date") <= ANY(Past event filter[To])),

AND(
LOOKUP([_THISROW].[Name], "GMONITORING", "Event ID", "Conducted Date") >= ANY(Past event filter[From]),
LOOKUP([_THISROW].[Name], "GMONITORING", "Event ID", "Conducted Date") <= ANY(Past event filter[To])),

AND(
LOOKUP([_THISROW].[Name], "WMONITORING", "Event ID", "Conducted Start Date") >= ANY(Past event filter[From]),
LOOKUP([_THISROW].[Name], "WMONITORING", "Event ID", "Conducted Start Date") <= ANY(Past event filter[To])),

AND(
LOOKUP([_THISROW].[Name], "Area Control", "Event ID", "Conducted Date") >= ANY(Past event filter[From]),
LOOKUP([_THISROW].[Name], "Area Control", "Event ID", "Conducted Date") <= ANY(Past event filter[To]))
),
TRUE


)
)
)
)

[Aman991]

Hi @AleksiAlkio ,

I've added code snippet.

[AleksiAlkio]

The time I'm able to use, here your formula is totally too long to find out the reason, unfortunately.

[Aman991]

SWITCH(
ANY(Past event filter[Event Type]),
in(Past event filter[Event Type], {"SpüMo Totvögel (Sondererfassung Totvögel)", "SpüMo Totvögel"}), AND(
LOOKUP([_THISROW].[Name], "MONITORING", "Name", "Conducted Date") >= ANY(Past event filter[From]),
LOOKUP([_THISROW].[Name], "MONITORING", "Name", "Conducted Date") <= ANY(Past event filter[To])
),
 
Okay,
this was the code that is used for selecting the row from table if it occurs in the specific date range that is added in a filter.

[AleksiAlkio]

Few comments without knowing your structures..
#1 - ANY(Past event filter[Event Type]) This may be different time to time if you have only one record in this table
#2 - in(Past event filter[Event Type], {"SpüMo Totvögel (Sondererfassung Totvögel) IN() expression needs two parameters.. a value and a list. Your first parameter is a list. Try to wrap it with ANY().
#3 - ANY(Past event filter[From]) This can be different time to time as well
#4 - ANY(Past event filter[To]) same as here