Hi,
I am trying to do a lookupvalue in a measure based on two filters in a different table. This lookupvalue is part of a much larger function with multiple ifs.
Here is a simplified breakdown of my tables:
Master tables (from database)
1) Receivables
Ex: main columns: Month, Customer, Receivables
MONTH | CUSTOMER | RECEIVABLES |
July 2017 | A | $100,000 |
July 2017 | C | $150,000 |
August 2017 | B | $200,000 |
| August 2017 | B | $50,000 |
| August 2017 | C | $10,000 |
2) Invoices
Ex: main columns: Month, Customer, Invoices
MONTH | CUSTOMER | INVOICES |
July 2017 | A | $15,000 |
July 2017 | A | $10,000 |
| July 2017 | C | $5,000 |
| August 2017 | B | $10,000 |
| August 2017 | B | $5,000 |
Created tables
3) Calendar
- Columns: Date (budget year July - June by days), Month No., Rank (month no. based on budget year)
- This table is linked to both my 'Receivables' and 'Invoices' tables by "Month" to "Date" of 'Calendar'
4) YTD Summary
- Columns: Customer, YTD Receivables, YTD Invoices
CUSTOMER | YTD RECEIVABLES | YTD INVOICES |
| A | $100,000 | $25,000 |
| B | $250,000 | $15,000 |
| C | $10,000 | $5,000 |
| D | - | - |
| E | - | - |
What I am trying to achieve:
I want a measure of the # invoiced days overdue --> the simplified calculation for this is:
= (Total Receivables month 1/Total Sales month 1)*30 (# days)
There are many ifs in this formula that require a 'lookupvalue' for the sum of sales from previous month(s) (month-1 or I used rank -1). Not only do I want to grab the sum of sales from a previous month but for a specific client.
///
I am able to achieve this lookupvalue measure based on the month alone:
Overdue table
I have another table for # invoiced days overdue:
- Columns: YTD Receivables, YTD Sales, Rank (month no. for budget year), invoiced days overdue, Month
| YTD Receivables | YTD Sales | Rank | # Invoiced Days Overdue | Month |
| $250,000 | $30,000 | 1 | 180 | July 2017 |
| $260,000 | $15,000 | 2 | 180 | Aug 2017 |
| | | | |
# Invoiced Days Overdue =
IF('Overdue table'[YTD Receivables] = 0,0,
IF('Overdue table'[YTD Sales] > 'Overdue table'[YTD Receivables], divide ('Overdue table'[YTD Receivables],'Overdue table'[YTD Sales])*30,
IF((LOOKUPVALUE('Overdue table'[YTD Sales], 'Overdue table'[Rank],'Overdue table'[Rank]-1)>('Overdue table'[YTD Receivables]-'Overdue table'[YTD Sales])), 30 + (('Overdue table'[YTD Receivables]-'Overdue table'[YTD Sales]/(LOOKUPVALUE('Overdue table'[YTD Sales], 'Overdue table'[Rank],'Overdue table'[Rank]-1))*30,...,180 this continues for 5 months back and '180' is at the end.
In the simplified tables the #Invoiced days overdue is equal to 180; however, in my actual data there is more variation in the values. The bolded section of the formula is what I would like to create for my summary table but again, to filter by Customer and Month.
I.e. if we are in August 2017 (Rank 2) client B, I would like my lookupvalue to sum the sales from Rank 1 for client B only.
