Comparing a single date to a list of dates

[bradlegassick]

I have to compare a list of invoice dates, from the "Related Invoices". The single "Start" date is entered in the dashboard filter controls. I have tried the following expression & a bazillion variations & I have only succeeded in getting random data output. So I am clearly not doing this right!

My dashboard filter controls that are working so far are in this expression:

AND(
IN([link_Venue],Carnz | Venues[Venue_ID]),

IF(ISBLANK(Index(Dashboard Reports[report_Supplier], 1)),FALSE,
IN([Supplier_Venue_Name],Dashboard Reports[report_Supplier])),

IF(ISBLANK(Index(Dashboard Reports[report_Venue], 1)),TRUE,
ISNOTBLANK(INTERSECT(SPLIT(Dashboard Reports[report_Venue],","),[Related Invoices][Venue_Name])))
)

However the minute I try to add the date filter I get weird output.

I have added these 2 expressions into the above expression BUT they are clearly not working for me. Any pointers or help is greatly appreciated.

IF(ISBLANK(Index(Dashboard Reports[report_Start_Date], 1)),FALSE,
ISNOTBLANK(INTERSECT(SPLIT(INDEX(Dashboard Reports[report_Start_Date],1),","),[Related Invoices][Invoice_Date]))),

IF(ISBLANK(Index(Dashboard Reports[report_End_Date], 1)),TRUE,
ISNOTBLANK(INTERSECT(SPLIT(INDEX(Dashboard Reports[report_End_Date],1),","),[Related Invoices][Invoice_Date])))

Thank you. Brad

[Suvrutt_Gurjar]

Could you mention any specific reasons you are using SPLIT() and INDEX() in the expression below?

SPLIT(INDEX(Dashboard Reports[report_Start_Date],1),",")

[bradlegassick]

I thought I needed to get it into a specific "List" format for comparing the lists.

[Suvrutt_Gurjar]

Thank you. Could you update if the expression Dashboard Reports[report_Start_Date] returns a single value and if [report_Start_Date] is a date or datetime column?

[bradlegassick]

[report_Start_Date] is a Date column type. Dashboard Reports[report_Start_Date] returns this 3/1/2023 12:00:00 AM Thanks for the help so far. Sorry on further checking the Dashboard Reports[report_Start_Date] returns 1/03/2023 for the Expression Result, however when you expand the output of the result to show the calculation you get the attached visual output.

[bradlegassick]

Thx , I have had to go to a football game & willl check in a few hours the
exact column types. I am not sure if they are simply date or are date
time.--


Please don't hesitate to contact me if you have any questions.


Kind regards

Brad


Brad Legassick

Business Automator

m: 0447 285 025

e: brad@appmate.com.au

w: www.appmate.com.au

[Suvrutt_Gurjar]

---

@bradlegassick wrote:

ISNOTBLANK(INTERSECT(SPLIT(INDEX(Dashboard Reports[report_Start_Date],1),","),[Related Invoices][Invoice_Date])))

---

Could you try instead

IN(ANY(Dashboard Reports[report_Start_Date]), SPLIT(TEXT([Related Invoices][Invoice_Date])," , "))

---

@bradlegassick wrote:

IF(ISBLANK(Index(Dashboard Reports[report_End_Date], 1)),TRUE,
ISNOTBLANK(INTERSECT(SPLIT(INDEX(Dashboard Reports[report_End_Date],1),","),[Related Invoices][Invoice_Date])))

---

Could you try instead

IN(ANY(Dashboard Reports[report_End_Date]), SPLIT(TEXT([Related Invoices][Invoice_Date])," , "))

[bradlegassick]

I have tried this & many other variations of the expression. I can still not get a really simple feature to work. Comparing a single date to a list & filtering out the dates that are outside of the Start & End dates. I will try to post some more later in the week to get a better picture of my data. Thanks so far for the help.

[Suvrutt_Gurjar]

---

@bradlegassick wrote:

Comparing a single date to a list & filtering out the dates that are outside of the Start & End dates

---

Thank you. The above highlighted text  is a vital additional point that I believe was either missing in the earlier description or maybe I missed it. If I missed it, sorry about that.

May we know where you are using this filter expression -meaning in a slice or elsewhere so that we could suggest an expression to try based on this latest update? 

In the meantime you could try the following sub expressions in your main expression inside the main for  AND()

MIN([Related Invoices][Invoice_Date]) >=ANY(Dashboard Reports[report_Start_Date])

MAX([Related Invoices][Invoice_Date]) <=ANY(Dashboard Reports[report_End_Date])

[bradlegassick]

Thanks Suvrutt, I am using the expression to filter a table of Suppliers. the goal is to filter by Supplier (done), optionally filter by Venue (done), filter related invoice records by start & end dates. This is the part that is causing a major headache. I am trying to get "instant" total $ Dollar purchase of the related invoices. I can total this on the Invoices table, however that is not ideal as it lists all the related invoices. So I am trying to do the total spend calculation on the Suppliers via the referenced related invoices. I can get the invoices totalled without a date filter. But the date filtering is just not being done right by me. It's easy to filter by single dates, but checking if a list of dates is in a date range is proving beyond me. Thanks for the help so far. I will take this up again tomorrow. 

[Suvrutt_Gurjar]

Thank you for the details. Please try the suggested sub expressions for the start and end date range.  Please do mention where you are using this filter ( meaning in column , slice etc) as requested.

[bradlegassick]

I am using the expression to filter a table of Suppliers, so I am trying to create a slice of the Suppliers. The Suppliers have Related Invoices, it is these records that I am trying to filter by the start & end date. I have been trying this version of your advice:

AND(

IN([link_Venue],Carnz | Venues[Venue_ID]),

IF(ISBLANK(Index(Dashboard Reports[report_Supplier], 1)),FALSE,
IN([Supplier_Venue_Name],Dashboard Reports[report_Supplier])),

IF(ISBLANK(Index(Dashboard Reports[report_Venue], 1)),TRUE,
ISNOTBLANK(INTERSECT(SPLIT(Dashboard Reports[report_Venue],","),[Related Invoices][Venue_Name]))),

AND(
IF(ISBLANK(Index(Dashboard Reports[report_Start_Date], 1)),FALSE,
MIN([Related Invoices][Invoice_Date]) >=ANY(Dashboard Reports[report_Start_Date])),

IF(ISBLANK(Index(Dashboard Reports[report_End_Date], 1)),TRUE,
MAX([Related Invoices][Invoice_Date]) <=ANY(Dashboard Reports[report_End_Date]))
)

)

The output is blank unless I select a Start date that is before my first related invoice date. Then the output is simply all the related invoices for that selected supplier. There doesn't appear to be any filtering of invoices by the dates. I select a start date after the first invoice date & I get no data returned.

[Suvrutt_Gurjar]

Thank you.  Is it correct understanding that you are trying to filter the parent table "Suppliers" based on some columns such as [Supplier_Venue_Name] and invoice dates range from the child invoice table? If there are invoices for those suppliers in the selected date range of dashboard filters , then that supplier is in ( in addition to some other conditions in the expression)

If so please try the below expression

AND(

IN([link_Venue],Carnz | Venues[Venue_ID]),

IF(ISBLANK(Index(Dashboard Reports[report_Supplier], 1)),FALSE,
IN([Supplier_Venue_Name],Dashboard Reports[report_Supplier])),

IF(ISBLANK(Index(Dashboard Reports[report_Venue], 1)),TRUE,
ISNOTBLANK(INTERSECT(SPLIT(Dashboard Reports[report_Venue],","),[Related Invoices][Venue_Name]))),

IF(OR(ISBLANK(Dashboard Reports[report_Start_Date]) , ISBLANK(Dashboard Reports[report_Eend_Date])),FALSE,

ISNOTBLANK(SELECT([Related Invoices][Invoice_Date], AND( [Invoice Date]>=ANY(Dashboard Reports[report_Start_Date]) , [Invoice Date]<=ANY(Dashboard Reports[report_End_Date])

))))

)

[Suvrutt_Gurjar]

And for filtering related records , please create a VC i the parent "Suppliers" table with an expression something like

SELECT([Related Invoices][Invoices Table Key], AND( [Invoice Date]>=ANY(Dashboard Reports[report_Start_Date]) , [Invoice Date]<=ANY(Dashboard Reports[report_End_Date])))

The VC type will be list with base type as reference and referenced table as Invoices table.

[bradlegassick]

Hi Suvrutt, thank you for all the help with this problem. I have a temporary work around, which Matt from MultitechVisions came up with. He uses this expression in the dashboard filter:

IF(ISBLANK(Index(Dashboard Reports[report_Start_Date], 1)),FALSE,
IN([Venue_Supplier_ID],Report_Invoices For Building_Reports[link_Invoices_Supplier])
)

What he is doing is checking if the Venue_Supplier ID is in the list of filtered invoices, if it is return them.

I know this doesn't really show me how to filter a list of dates by a single date, but it gets the totals I need. There is also the issue of lag when the system needs to update the values. However I can live with that for now.

If anyone does know a painless method of filtering a list of dates by a start & end date, please can you share it?

Thank you again for all your help.

[Suvrutt_Gurjar]

Hi @bradlegassick ,

Thank you for the update.

Excellent  to know you got a solution from Matt.

May I know if you got a chance to test the suggestion I made in the last post of mine.

[bradlegassick]

Hi Suvrutt, to be honest I got so busy in the week with my stock taking job I didn't get back to this until I had a call with Matt today. I will have time at the end of this week & will give it a go. I need to learn the method, so all avenues will be explpored. I will post back here around Friday with an update. 🙂

[bradlegassick]

Hi Suvrutt, I have tried the expression & there is still no clear filtering of the related invoices. I have attached a screenshot to show you the output. No matter the date ranges selected, the output is all invoices for the supplier. Nothing filtered by the date ranges. I am using a "Report" table for the filter constraints. The Parent table Suppliers has related invoices, the child records. The invoice table is joined to the Supplier via a Ref field [link_Invoices_Supplier]. This is not a part of the referenced Supplier table. All the references seem to be intact & working as expected in all other facets of the app. I am not sure if there is a guide to get filtering date lists, but this is consuming all my brain power & a smidgen of yours! I am really sorry & appreciate your help immensly. 

[Suvrutt_Gurjar]

 Thank you for the details. However the screenshots do not help much as on the right hand side there are no dates in the card view, so not able to understand the details. Also I am unable to understand what tables the view is based on on the right hand side. Left hand side of course seems to be filtering arrangement. At this point it will be good to know a snapshot of the relevant tables, and all column types and views used in the expression to proceed, During each post also the latest expression used will help.

Also you seem to be using new desktop UI. I would be personally apprehensive about using new desktop UI at this stage on a production app or extensive calculations because it is still in preview mode.

[bradlegassick]

Hi Suvrutt, thanks again for helping me here. I will revert the app to the legacy desktop & check the filter behaviour. For your other information: The view is a Dashboard view "Reports Dashboard" based on a "Reports Filter" view & a "Filtered Supplier Summary"  view. The tables of the views are: "Reports Filter" is based on the slice called "Dashboard Reports" from the "Reports" table. This table has dedicated columns for the various report filters & output files - Column types are "Date" for the Start & End Dates, Enum List for the Supplier & Venue filter columns, Text for the Invoice number filter. The Supplier table has the standard ref columns to the Invoice table. I have attached screenshots of the tables & vSupplier TableSupplier Table contSupplier Table cont2InvoicesInvoices contInvoices cont2Report Filter ViewSupplier Summary View (for the invoice totals)iews for your reference.

[Suvrutt_Gurjar]

Thank you. Hope "Dashboard Filter" slice is based on a single row being returned for the logged in user. Something like [Email]=USErEMAIL()

You may want to start with the expression in the Suppliers table in a VC as requested ( VC Type List with base type reference, referenced table Invoices).  This VC should return related invoices records based on Start and End dates selected in the dashboard filter. You may suitable name this VC as [Filtered date Range Invoices] or something similar.

SELECT([Related Invoices][Invoices Table Key], AND( [Invoice_Date]>=ANY(Dashboard Reports[report_Start_Date]) , [Invoice_Date]<=ANY(Dashboard Reports[report_End_Date])))

[bradlegassick]

Thanks again, I'll test this & post the results. I have the Current User system implemented & all reports are user specific & they have their own rows in the report table.

[Suvrutt_Gurjar]

---

@bradlegassick wrote:

I have the Current User system implemented & all reports are user specific & they have their own rows in the report table.

---

👍

---

@bradlegassick wrote:

Thanks again, I'll test this & post the results

---

👍