CODEWITHSUNDEEP
dynamicpowerbirelationshipaggregationpowerbi-embedded
Juliana Sader
Juliana Sader

Reputation: 1

Dynamic Aggregation and Relationships in Power BI

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.

Upvotes: 0

Views: 29

Answers (0)

Related Questions