Create Measure with multiple and variable Conditions

[manglegose]

Hi there,

I want to do some backtesting with stock market data via Power BI. This includes calculating the return of a certain strategy based on several filters.

In order to do that, I also need to remove duplicate data. Duplicate data means for me, that a ticker that is in my backtesting (or in the list below as an entry) already in my portfolio can't be bought more than once. In the example below the holding period of a position are 10 trading days. Trading days are translated via Date ID. In other words, Date ID consists only of trading days.

Here is some data that is extracted from my database and I added the column "How it should be" manually. I need a measure for that column. I also thought of a counter that adds up all the distances between the previous entries of a certain ticker. If they are >= 10 (or variable Holding time) it resets to 0 and counts on from there for the following entries.

[Data follows with the next post]

Since I started intensively with Power BI a few weeks ago column 3 to 5 are not calculated with measures, but within the data table.

I am trying to solve this for a few days, now, without any progress. So, I need your help for this one.

Any help is highly appreciated. Thank you in advance.

[v-stephen-msft]

Hi @manglegose ,

Sorry, I didn't find the [Holding Time in Trading Days] in sample data provided. How was the [Holding Time in Trading Days] created?

Best Regards,

Stephen Tao

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

[manglegose]

Hi @v-stephen-msft ,

Thank you for your message.

[Holding Time in Trading Days] is just a variable (or measure) that equals 10 in my example. I use it as a variable in several measures.

Measure: 

Holding Time in Trading Days = 10

Thanks in advance.

Regards,

manglegose

[manglegose]

Here are the formulas for the columns 3 to 5:

Duplicates = 
VAR CurrentDateID = 'Backtesting'[Date ID]
VAR CurrentTicker = 'Backtesting'[Ticker]

RETURN
IF(
    COUNTROWS(
        FILTER(
            'Backtesting',
            'Backtesting'[Ticker] = CurrentTicker &&
            'Backtesting'[Date ID] >= CurrentDateID - [Holding Time in Trading Days] &&
            'Backtesting'[Date ID] < CurrentDateID
        )
    ) >=1,
    "Yes",
    "No"
)

Holding Time Check = 
VAR CurrentDateID = 'Backtesting'[Date ID]
VAR CurrentTicker = 'Backtesting'[Ticker]
VAR CurrentDuplicates = 'Backtesting'[Duplicates]
VAR LastNoDateID =
    CALCULATE(
        MAX('Backtesting'[Date ID]),
        FILTER(
            'Backtesting',
            'Backtesting'[Ticker] = CurrentTicker &&
            'Backtesting'[Date ID] < CurrentDateID &&
            'Backtesting'[Duplicates] = "No"
        )
    )
VAR MinDateIDWithNo = 
    CALCULATE(
        MIN('Backtesting'[Date ID]),
        FILTER(
            'Backtesting',
            'Backtesting'[Ticker] = CurrentTicker &&
            'Backtesting'[Date ID] >= CurrentDateID - [Holding Time in Trading Days] &&
            'Backtesting'[Date ID] < CurrentDateID &&
            'Backtesting'[Duplicates] = "No"
        )
    )

VAR DuplicationCheck =
IF(
    (CurrentDateID - LastNoDateID >= [Holding Time in Trading Days] &&
    CurrentDateID - MinDateIDWithNo >= [Holding Time in Trading Days]),
    "No",
    "Yes"
)

VAR HoldingTimeCheck =
IF(
    COUNTROWS(
        FILTER(
            'Backtesting',
            'Backtesting'[Ticker] = CurrentTicker &&
            'Backtesting'[Date ID] >= CurrentDateID - [Holding Time in Trading Days] &&
            'Backtesting'[Date ID] < CurrentDateID &&
            'Backtesting'[Duplicates] = "No"
        )
    ) >= 1,
    "Yes",
    "No"
)

RETURN
IF(
    DuplicationCheck = "Yes" && HoldingTimeCheck = "Yes",
    "Yes",
    "No"
)

Distance Last No Duplicates = 
VAR CurrentDateID = 'Backtesting'[Date ID]
VAR CurrentTicker = 'Backtesting'[Ticker]
VAR LastNoDateID = 
    CALCULATE(
        Max('Backtesting'[Date ID]),
        FILTER(
            'Backtesting',
            'Backtesting'[Ticker] = CurrentTicker &&
            'Backtesting'[Date ID] < CurrentDateID &&
            'Backtesting'[Duplicates] = "No"
        )
    )
VAR PreviousDateID = 
    CALCULATE(
        Max('Backtesting'[Date ID]),
        FILTER(
            'Backtesting',
            'Backtesting'[Ticker] = CurrentTicker &&
            'Backtesting'[Date ID] < CurrentDateID
        )
    )

Return
IF(
    CurrentDateID - PreviousDateID < [Holding Time in Trading Days],
    CurrentDateID - PreviousDateID,
    CurrentDateID - LastNoDateID
)

[manglegose]

Here is the sample data:

Ticker	Date ID	Duplicates	Holding Time Check	Distance Last No Duplicates	How it should be
AAPL	12	No	No	12	No
AAPL	443	No	No	431	No
AAPL	445	Yes	Yes	2	Yes
AAPL	446	Yes	Yes	1	Yes
AAPL	447	Yes	Yes	1	Yes
AAPL	448	Yes	Yes	1	Yes
AAPL	449	Yes	Yes	1	Yes
AAPL	470	No	No	27	No
AAPL	471	Yes	Yes	1	Yes
AAPL	472	Yes	Yes	1	Yes
AAPL	474	Yes	Yes	2	Yes
AAPL	475	Yes	Yes	1	Yes
AAPL	476	Yes	Yes	1	Yes
AAPL	477	Yes	Yes	1	Yes
AAPL	478	Yes	Yes	1	Yes
AAPL	479	Yes	Yes	1	Yes
AAPL	481	Yes	No	2	No
AAPL	483	Yes	No	2	Yes
AAPL	488	Yes	No	5	Yes
AAPL	490	Yes	No	2	Yes
AAPL	495	Yes	No	5	No
AAPL	496	Yes	No	1	Yes
AAPL	542	No	No	72	No
AAPL	543	Yes	Yes	1	Yes
GOOGL	13	No	No	13	No
GOOGL	15	Yes	Yes	2	Yes
GOOGL	16	Yes	Yes	1	Yes
GOOGL	17	Yes	Yes	1	Yes
GOOGL	31	No	No	18	No
GOOGL	34	Yes	Yes	3	Yes
GOOGL	35	Yes	Yes	1	Yes
GOOGL	36	Yes	Yes	1	Yes
GOOGL	37	Yes	Yes	1	Yes
GOOGL	38	Yes	Yes	1	Yes
GOOGL	39	Yes	Yes	1	Yes
GOOGL	40	Yes	Yes	1	Yes
GOOGL	41	Yes	No	1	No
GOOGL	42	Yes	No	1	Yes
GOOGL	43	Yes	No	1	Yes
GOOGL	44	Yes	No	1	Yes
GOOGL	45	Yes	No	1	Yes
GOOGL	46	Yes	No	1	Yes
GOOGL	47	Yes	No	1	Yes
GOOGL	48	Yes	No	1	Yes
GOOGL	49	Yes	No	1	Yes
GOOGL	50	Yes	No	1	Yes
GOOGL	52	Yes	No	2	No
GOOGL	53	Yes	No	1	Yes
GOOGL	54	Yes	No	1	Yes