Get max invoice date by customer and item number when selecting a date in dashboard

[St_Data19]

Hello,

I'd really appreciate some help. I have a requirement, in which I need to get the max of invoice date that is <= to the date a user will select in the dashboard. Basically, we have monthly sales files coming in and for example, if I select the Jan 2019 file, I need to get the latest invoice by item and customer before the end date of that fiscal month for all of the items listed in that file. 

Example:

I have 3 invoices (coming from my invoice table and joining to the file via item number)

Customer ,Item#, InvoiceNumber, InvoiceDate

A, 1234, 786599,1/25/2019

A, 1234, 99999, 12/22/2018

A, 1234, 70888, 1/11/2019

The end of the fiscal month for the Jan 2019 file will be 1/27/2019, which is the Date field. So, the invoice I'd want to get when the user selects that file/date is the first one with date 1/25/2019.

The problem is, if I use Max(Aggr(Max({<InvoiceDate = {"<=$(=Date)"}>} InvoiceDate),Customer,ItemNumber)), I'm able to get that date, but it gives me a completely random invoice in my dimension. I mean, it'll give me an invoice for that item and customer, but it does not match my expression. For example,  in this case, it'll give me the invoice with date 12/22/2018, when it should give me the one with date 1/25/2019. I've tried doing this in the script, I've tried using FirstSortedValue as well. I don't quite understand where the issue is.  Each item number will have multiple invoices by different customers, and I just want the latest one for comparison to see if our invoice price matches what is in the file. Any input will be appreciated.

Thank you.

[Kushal_Chawda]

@St_Data19  try below

=aggr(FirstSortedValue(distinct InvoiceDate -InvoiceDate),Customer,ItemNumber)

[St_Data19]

Didn't work ☹️

[Kushal_Chawda]

@St_Data19  where you are using this expression? What are dimensions?  Based on that what is expected output?

[St_Data19]

So, currently I'm using this expression in a straight table. I did this in the script too, but no dice. 

Script:

FileTable:

LOAD

           ItemNumber - Key

           Date

           Field 3

           Field 4 etc...

FROM File;

           JOIN(FileTable) - Join Invoice info

            LOAD

                      ItemNumber - Key

                      InvoiceNumber

                      InvoiceDate

                     InvoiceQty

FROM Invoice (qvd);

The expected output should be the max(invoicedate) <= Date (from file), grouped by/aggregated by customer, itemnumber.

Straight Table:

Dimensions

Customer

ItemNumber

InvoiceNumber

InvoiceQty

Expression (mentioned above)

If my chart looks like below (invoices listed by item and customer)

Customer    Item    Invoice   Qty  InvoiceDate

A                       123        89777      5      1/11/2019

A                     123         76555       6        12/21/2018

A                    123           44455      7        1/24/2019

A                    123            3444       10      3/4/2015

Assume I select 1/26/2019 from the Date field, which comes from the file, and is associated to the Jan 2019 file. My output should look like below

Customer    Item    Invoice   Qty  InvoiceDate

A                      123     44455      7        1/24/2019

But instead I get something like below

Customer    Item    Invoice   Qty  InvoiceDate

A                    123            3444       10      1/24/2019

It gave me the correct date, but gave me the invoice from March 2015, which doesn't make sense.  My assumption is that all of the invoice dates for a specific item are being associated with the date from the file, since there's only one date in the file, which is end of the fiscal month for that file. So it's getting confused, but how to rectify this? There's gotta be a way.  

[St_Data19]

So I think I found a work around. I use Max(InvoiceDate) <= Date in my expression (aggregrated by customer and ItemNumber). It lists all of the invoice numbers up until the max date. Nothing seemed to be working until I had a thought. I went into the dimension limits and limited the invoice date field to the largest or highest date, and then it only gave me that one record with the correct invoice number, date, etc.. There probably is a way to do this via script/expression, but this seems to be working for now and is giving me the desired result (fingers crossed). I'm on a time crunch, so I'll stick with this for now. Thanks for your help!!