DAX: How to sum values between two dates from unrelated table?

0
votes

I have three tables,

Supplier
    SupplierID

SupplierRebate
    SupplierID (Is a key to Supplier[SupplierID]
    DateStart
    DateEnd
    TurnoverMin
    RebateRate

ProductBought
    SupplierID (Is a key to supplier[SupplierID]
    TotalPrice
    DateInvoice

ProductBought and SupplierRebate are both linked to Supplier. I am trying to make the sum of the total price, during each rebate. I want for each rebate, for a perticular supplier, to Sum total price when the DateInvoice is between DateStart and DateEnd.

It looked so simple, and I've spent hours trying to understand, but I just can't seem to get DAX into my brain. I've tried Sum, SUMX, Filters, Calculate and a few others. I just can't seem to understand Dax.

Thanks for the help

2
powerbidax

2 Answers

0
votes

I think if you create a both side relationship between SupplierID <-> SupplierRebate and SupplierID <-> ProductBought and create a single row card for Total Price with DateInvoice as Slicer , it should do the trick. DAX won't be required then.

0
votes

The below doesn't require bidirectional relationships, which should in everything I've ever read/watched/heard about DAX should pretty much be avoided at all costs.

SUMMARIZE ( SupplierRebate, Supplier[SupplierID], SupplierRebate[DateStart], SupplierRebate[DateEnd], SupplierRebate[TurnoverMin], SupplierRebate[RebateRate], "Total Purchased", CALCULATE ( SUM ( ProductBought[TotalPrice] ), FILTER ( ProductBought, ProductBought[DateInvoice] <= MAX ( SupplierRebate[DateEnd] ) && ProductBought[DateInvoice] >= MAX ( SupplierRebate[DateStart] ) ) ) )