Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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...
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |