Problem with Relationship and Getting Aggregates

[iamprajot]

Hi

Link to the file is Here.

I have 3 Tables

Orders - Order Related Details with Unique Column, OrderID, OrderDate, ... and so on.

Returns - Contains a single column, ReturnID which contains OrderID that were returned.

Expenses - It contains 2 columns, Expense Date and the Expense Amount.

This Expenses Table is niether related to Orders and nor to Returns as the expenses are of different dates.

Now what I want is to create Matrix Table in which I want to show Sold Amount from Orders Table, Returned Amount from Return & Orders Table and Expense for that Month from Expenses Table.

In my actual Data I have created relationships in Orders and Returns to get related info.

These tables have no relationship to Expenses Table and I want to create a relationship based on Month and Year so that I could pull the Expenses into the same row in the Matrix Table.

But relationship is not working in my actual Data, so is there a way to get the Expense Amount for the Month into the Table or to recreate some kind of relationship.

NOTE:-Keep that in mind that Relationship in this Data is working but not working in my actual data due to multiple relationships and duplicate OrderID's and ReturnID's so I have inactive the relationships and even if I active them then I am getting blank results.

[v-shex-msft]

HI @iamprajot,

I think you need to write a formula to manually get available order date range in summary row contents, then find out matched Expenses records.

Sample formula:

Total Expense = 
VAR _valid =
    MINX ( VALUES ( Orders[OrderDate] ), [OrderDate] )
RETURN
    IF (
        _valid <> BLANK (),
        CALCULATE (
            SUM ( Expenses[Expense] ),
            FILTER (
                ALL ( Expenses ),
                [ExpenseDate]
                    IN CALENDAR (
                        DATE ( YEAR ( _valid ), MONTH ( _valid ), 1 ),
                        DATE ( YEAR ( _valid ), MONTH ( _valid ) + 1, 1 )
                            - 1
                    )
            )
        )
            + 0
    )

Regards,

Xiaoxin Sheng

[iamprajot · Accepted solution]

Thanks.
It is working in the attached sample data but not working in the actual data.
I somehow need to use the logic provided in the actual scenario or I was thinking of creating a summary table to get the values based on matching conditions.