Search code examples
ssaspowerbidax

DAX dynamic filter using two columns from related table

I need to make some simple calculation using DAX, but I am not sure how do I achieve same result as I did in Excel's formulas. First of all my data structure looks as on screenshot:

Matrix with data

Measures are coming from one table, and the PactDelivered is an attribute from related table which is connected by relationship.

All I really have to do is to translate formula as below into DAX: enter image description here

There is no problem when it comes to calculate B3-C3-D3, but I have a problem how do I calculate the - B4 + C4 part. Of course it should be dynamic, so if it comes to calculate result for Package number 10, then it'd take figures from number 10 and 11.

EDIT:

After modification of code from Alexis Olson I got something like below, but it's not working yet. Please note the PactDelivered is in other table than measures are. Not knowing why, but it's calculating the result on packs according to NextPack value. I tried adding another variable which was basically NextPack -1, and tried calculating the sums from return using it, but didn't work as well.

Cancellations:=

var NextPack = MAX('Order'[PactDelivered]) + 1

var NextOrders = CALCULATE(SUM('Fact DropOff'[OrdersSentOut]) + sum('Fact DropOff'[OrdersReturned]), 'Order'[PactDelivered] = NextPack)

return  SUM ('Fact DropOff'[OrdersSentOut]) - SUM ('Fact DropOff'[OrdersReturned] ) - SUM('Fact DropOff'[ActiveSubscriptions]) -  NextOrders

after edits

EDIT2: Sample data from fact: fact

From dimension: dimension

Relationship: Relationship

Solution

  • Try a measure like this:

    Measure = 
    VAR NextPact = MAX(Orders[PactDelivered]) + 1
    VAR NextOrders = CALCULATE(SUM(Orders[OurdersSentOut]) - SUM(Orders[OrdersReturned]),
                         Orders[PactDelivered] = NextPact)
    RETURN SUM(Orders[OurdersSentOut]) - SUM(Orders[OrdersReturned]) -
           SUM(Orders[ActiveSubscriptions]) - NextOrders

    First, we calculate the next index by taking the current index and adding 1. Then you calculate sent out minus returns for that index. Finally, subtract that from the current row calculation.