Forum Discussion
Filtering if date is before today on 3 separate fields
I have 3 fields in TableA which are Expected_resolution_UTC, Expected_plan_UTC and Expected_response_UTC. I need to filter a visual (Card) from TableB (there is a link) based on whether any of the above fields has a date in the past. Adding each as a Filter will not work because only one of the Fields may have a date in the past so I am guessing that I need a measure that creates a value based upon an IF statement?
Can anyone advise how I need to do this?
In my opinion youre on a too complicated approach. Always keep your goal in mind.
From my point of view the goal is to have a column, which is your criteria check and only shows true or false. Then you can create a simple card with the count of your ID filtered by criteria check=true.
If the criteria check from before didnt work you can also try to use multiple IF functions:
if(date1<today();True;
if(date2<today();True;
if(date3<today();True;False)))
4 Replies
You could add an additional calculated column to check if one of the dates fulfills your criteria an then filter by the created column.
criteria check= if( check1 || check 2 || check3; "True" ; "False")
replace the checks with your date logic, eg. date<today
You can chain multiple checks with || , which is like an OR.Thanks CoachVE, i tried that but it returned a positive for every line. I have got a little further in that I have now got 3 calculated fields that use the below (or similar) dependant on which date I am checking.
OverdueResolution = COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Resolution_Expiration_Date_UTC]))&&[Expected_Resolution_Expiration_Date_UTC]<NOW()))I now just need to combine all 3 in to a single card, but not sure how i can combine these?OK, I am now 1 step further on and can combine the count rows, however I only need to count each row once, i.e. if the date is passed in all 3 fileds I only need to count once. What I have so far is :
OverdueSR = IF(ISBLANK(COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Resplan_Expiration_Date_UTC]))&&[Expected_Resplan_Expiration_Date_UTC]<NOW()))+COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Resolution_Expiration_Date_UTC]))&&[Expected_Resolution_Expiration_Date_UTC]<NOW()))+COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Responded_Expiration_Date_UTC]))&&[Expected_Responded_Expiration_Date_UTC]<NOW()))),"0",COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Resplan_Expiration_Date_UTC]))&&[Expected_Resplan_Expiration_Date_UTC]<NOW()))+COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Resolution_Expiration_Date_UTC]))&&[Expected_Resolution_Expiration_Date_UTC]<NOW()))+COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Responded_Expiration_Date_UTC]))&&[Expected_Responded_Expiration_Date_UTC]<NOW())))
