Forum Discussion

thomasAL's avatar
thomasAL
Frequent Visitor
5 years ago
Solved

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

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:

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

     

1 Reply

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.