Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Substract Values from Same column dynamically based on another field.

I have a table (See below) with prices for different years     I have 250 Trade Days in each contract period from 11/01 to 10/31 each year.   I need to dynamically select (what if) table o...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    Please try to update the formula of your measure "Profit" just like below(the red font part is newly add):

    Profit =
    CALCULATE (
        SUM ( Winter_Contracts_Zema[Price] ),
        FILTER (
            ALL ( Winter_Contracts_Zema[Trade Day - Buy] ),
            Winter_Contracts_Zema[Trade Day - Buy] = [Buy_Trade Value]
        )
    )
        - CALCULATE (
            SUM ( Winter_Contracts_Zema[Price] ),
            FILTER (
                ALL ( Winter_Contracts_Zema[Trade Day - Buy] ),
                Winter_Contracts_Zema[Trade Day - Buy] = [Sell_Trade Value]
            )
        )

    Best Regards

    Rena

  • Anonymous's avatar
    Anonymous
    6 years ago

    I solved it by using the value function in my measure! thanks to everyone who took the time to read this!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I created this measure

Profit = CALCULATE(SUM(Winter_Contracts_Zema[Price]), Winter_Contracts_Zema[Trade Day - Buy] = [Buy_Trade Value]) - CALCULATE(SUM(Winter_Contracts_Zema[Price]), Winter_Contracts_Zema[Trade Day - Buy] = [Sell_Trade Value])
 
But I get this error
 
A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
 
By trade Value is a parameter table i Created to emulate this dax calculation
 
Profit = CALCULATE(SUM(Winter_Contracts_Zema[Price]), Winter_Contracts_Zema[Trade Day - Buy] = 148  - CALCULATE(SUM(Winter_Contracts_Zema[Price]), Winter_Contracts_Zema[Trade Day - Buy] = 244)
 
I need the trade day filter values to change dynamically by end user, like a parameter. Any ideas?
 
All the help is highly appreciated
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

Please try to update the formula of your measure "Profit" just like below(the red font part is newly add):

Profit =
CALCULATE (
    SUM ( Winter_Contracts_Zema[Price] ),
    FILTER (
        ALL ( Winter_Contracts_Zema[Trade Day - Buy] ),
        Winter_Contracts_Zema[Trade Day - Buy] = [Buy_Trade Value]
    )
)
    - CALCULATE (
        SUM ( Winter_Contracts_Zema[Price] ),
        FILTER (
            ALL ( Winter_Contracts_Zema[Trade Day - Buy] ),
            Winter_Contracts_Zema[Trade Day - Buy] = [Sell_Trade Value]
        )
    )

Best Regards

Rena

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you!! my profit measure is now working!

     

    This is probably very simple, as it is in excel but I cant figure it out in Power Bi

    I need to count the number of times my profit measure gave > 0 results.

     

    =COUNTIF(Profit>0")

     

    But i get an error in power Bi as it doesnt accept a measure as a filter. Any ideas? Im stuck in something so simple.

     

    I also tried Calculate(Count(Winter_Contracts_Year)), [profit] > 0)

     

     

    I  highly appreciate the best practice here.

     

     

     

    From my 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I solved it by using the value function in my measure! thanks to everyone who took the time to read this!