Calculate spread value between Hub location for a same commodity "Power" and same maturity: A - B

[thomasAL]

Dear Power BI community,
I am trying to calculate a power spread price on a specific maturity (for example "Y+1-BL") between one hub location (for example "De" -> Germany) and another (for instance "NL" -> Netherlands). In order to do so, I duplicated the table containing the product values for price data so I can operate the operation A - B with knowing that A & B points at power price data for 2 different hub locations. I have displayed the power price values for one hub "DE" in my 1st visual table and the power price values for other hubs (excluding "DE") in another visual Table. Now I would like to operate: A - B on the power price values. How can I do that? I tried to create a new measure with applying the following DAW statement but it didn't work: A -B = ([Product Value A], Filter(Dim, [_dlvHub] = "DE")) - Calculate([Product Value B], Filter(Dim, [_dlvHub] <> "DE"))

Product Value A is a column of the "marketpricedata" table

Product Value B is the column of "table B" which is a dupplicated table of  "marketpricedata" table, because I thought I couldn't operate arithmetic operations like A-B on values from a same column..

_dlvHub is a column which contains hub locations from the "Dim" Table

Can you help me please? Thanks in advance for your precious support!

For more info, here is a screen caption of what I am trying to display:

[v-alq-msft · Accepted solution]

Hi, @thomasAL 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

You may create a calculated table as below.

Table = 
ADDCOLUMNS(
    DISTINCT(Table1[Date]),
    "V1",
    CALCULATE(SUM(Table1[Value]),FILTER(ALL(Table1),[Date]=EARLIER(Table1[Date])))-CALCULATE(SUM(Table2[Value1]),FILTER(ALL(Table2),[Date]=EARLIER(Table1[Date]))),
    "V2",
    CALCULATE(SUM(Table1[Value]),FILTER(ALL(Table1),[Date]=EARLIER(Table1[Date])))-CALCULATE(SUM(Table2[Value2]),FILTER(ALL(Table2),[Date]=EARLIER(Table1[Date]))),
    "V3",
    CALCULATE(SUM(Table1[Value]),FILTER(ALL(Table1),[Date]=EARLIER(Table1[Date])))-CALCULATE(SUM(Table2[Value3]),FILTER(ALL(Table2),[Date]=EARLIER(Table1[Date]))),
    "V4",
    CALCULATE(SUM(Table1[Value]),FILTER(ALL(Table1),[Date]=EARLIER(Table1[Date])))-CALCULATE(SUM(Table2[Value4]),FILTER(ALL(Table2),[Date]=EARLIER(Table1[Date])))
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.