Related Table

[ateetkoomar]

I have three tables.

  1. FactTable

    Dim_Time_Days_Sales (yyyymmdd)

    Dim_Time_Days_Refund (yyyymmdd)

    Sales £

    Refund £

2. Dim_Time_Days_Sales

   Date (yyyymmdd)

3. Dim_Time_Days_Refund

   Date (yyyymmdd)

I want to be able to work out the value of Sales after deducting Refund.

So a customer might buy something on 1st Nov and return on 5th of Nov. But I want to be able to say what was the sales amount after deducting refund. But as they are both occurring on different dates, how would I do that?

[v-juanli-msft]

Hi @ateetkoomar

How would you like the to calculate " the sales amount after deducting refund"?

Could you give an example?

I make a test, could you correct me if i understand you right?

create a measure

sales amount = SUM(FactTable[Sales])-SUM(FactTable[Refund])

Best Regards

Maggie

[ateetkoomar]

the table you have done is exactly what I need.

[ateetkoomar · Accepted solution]

I have managed to sort this out. I created a fourth calendar and linked them through that.

[ateetkoomar]

So basically, I want to work out the sales amount for the 1st after deducting the refund which happened on the 5th from my original example.

[AkhilAshok]

Try creating a single Date table and add relationships to the 2 Date columns in your fact table. Keep the Sales Date relation active, and other inactive. Below code should give you the net Sales amount for a selected time frame.

Net Sales Amount =
SUM ( FactTable[Sales] )
    - CALCULATE (
        SUM ( FactTable[Refund] ),
        USERELATIONSHIP ( FactTable[Dim_Time_Days_Refund], Date[Date] )
    )