I have a dataset where each row provides information about invoices that are either due or already paid. My dataset looks a little like this:
| Store | Product | Value | Installment Number | Due Date | Payment Date | Purchase Date |
|---|
What I’m trying to do is create another table that summarizes information for each date. For example, the new table should look like this:
| Date | Value Received | Value Expected |
|---|
Where: Value Received = sum of Value where Payment Date = Date
and Value Expected = sum of Value where Due Date = Date
How can I solve these issues? Is there a better way to approach this?
I already have a separate table with all possible dates, which I intended to use for this summary. However, I’ve encountered the following issues:
When I create a calculated column in the date table, I’ve tried using the following formula in a calculated column in the date table:
SUMX(
FILTER(
payment,
payment[Due Date] = summarize[Date]
),
payment[Value]
)
The resulting summary table doesn’t interact dynamically with filters applied to the original dataset.
Relationship conflicts: If I try to link the date table to the dataset, I can’t simultaneously connect it to both the Due Date and the Payment Date fields because it creates ambiguous paths in the relationship model.
