Question about DAX/measures

[joe321]

Greetings.

I have a parent company with 5 subsidiaries. If the parent company has income for a given year, it can offset that income with a loss in any of the subsidiaries. The wrinkle is that there is an order used to absorb the losses that varies by year.

Using table 2 below, If the parent company has income in year 2012, it would first check to see if sub 1 has a loss and offset it by the loss followed by sub 2 to sub 5. In 2013, using he example below, it would start with sub 5 and go to sub 4, 3, 2 and 1 to offset income.

In table 3, the parent company has 5000 in income in year 2012. Using table 2, I would offset that income by the order of 2,3,4,5,6. Company 2 has a loss of (2000) and company 4 has a loss of (3500) I could use 3000 of that 3500 loss. The (1000) loss of company 6 will not be picked up as the 5000 in income was already used up before getting there.

Can this be modeled in DAX? If so, I'd like pointers on a starting approach. Could a hierarchy be used? A co-worker did this in regular Excel and it was pretty involved and I am just curious if it could be simplified using DAX.


Table1
CompanyID/ ParentID/ Description
1 / 1 Parent Company
2 1 xyz - Subsidiary 1
3 1 abc -Subsidiary 2
4 1 ghi - Subsidiary 3
5 1 klm - Subsidiary 4
6 1 nop - Subsidiary 5

Table2
Year/ Company ID/ order to absorb loss
2012 2 1
2012 3 2
2012 4 3
2012 5 4
2012 6 5
2013 2 5
2013 3 4
2013 4 3
2013 5 2
2013 6 1


Table3
Year/ Company ID/ Income(loss)
2012 1 5000
2012 2 (2000)
2012 3 1000
2012 4 (3500)
2012 5 500
2012 6 (1000)

 

[ImkeF]

Abolutely: Using EARLIER, you can create a column with a running total that considers the individual order sequences. sth like: CALCULATE(SUM(Income/loss),FILTER(Table2, Order<EARLIER(Order) && Year=EARLIER(Year))
Everything else are some if-thens to exclude positive values and to pick the "remaining" amount where the last portion of the loss will be used.
Would recommend to add the loss of the parent company with order No 0 into table2.

hth, Imke