Lookup value measure with specific range

[CD1]

Hi,

I am trying to create a measure based on different ranges of sums for the same parameter from a different table...

In simplified terms, these are the tables I have:

Master Tables (from databases):

1) Receivables table:

- Columns: month, clients, receivables 

Ex: 

MONTH	CLIENT	RECEIVABLES
July 2017	A	$100,000
August 2017	B	$150,000
August 2017	A	$200,000

*There are instances where there are several lines for the same client within the same month with different receivables values

2) Invoices table: 

- Columns: month, clients, sales

Ex:

MONTH	CLIENT	INVOICES/SALES
July 2017	A	$15,000
August 2017	B	$10,000

Created Summary Table from Master Tables:

o   Columns: Customer, YTD Receivables, YTD Sales

o   Ex:

CLIENT	YTD R	YTD S
A	$300,000	$15,000
B	$200,000	$10,000

Essentially what I am trying to achieve is to calculate the # days overdue for invoices. This formula is quite long and contains many 'IFs'. I am able to achieve this calculation based on the months alone but not on months AND clients. 

-  Simplified formula: Total Receivables/Total Invoices for current month * #days (in month)

-  Issue: Require the sum of invoices for the same client in previous month(s)

--> How can I do a ‘lookupvalue’ of a sum (in invoices table) for a specific range? Is this possible?

(Relationships: There is a 'Calendar' table linked to my 'Receivables' and 'Invoices' tables)

I have added a 'rank' column linked to my 'months' to substitute 'month no.' for my company's budget year. Next, when trying to pull the sum from a previous month I used 'Rank-1' in my 'lookupvalue' formula. The issue is that I need my measure to filter on both the client and specific month and the result of the "previous month's invoices sum" is an error message or it gives me the total invoices for all months and clients. Not sure if this is clear enough... 

[v-yulgu-msft · Accepted solution]

Hi @CD1,

Please try below formula.

Rank =
CALCULATE (
    COUNTROWS ( 'Table2' ),
    FILTER (
        ALL ( Table2 ),
        'Table2'[Client] = EARLIER ( Table2[Client] )
            && 'Table2'[Month] < EARLIER ( 'Table2'[Month] )
    )
)
    + 1


Previous month Invoice =
LOOKUPVALUE (
    Table2[Invoice],
    Table2[Client], Table2[Client],
    Table2[Rank], Table2[Rank] - 1
)

Best regards,

Yuliana Gu