[German Stock Market] Evaluate a condition, return 0 or 1, sum it and calculate a percentage

[Anonymous]

Hello dear Power BI Forum,

I am creating a report about the German stock market index DAX40 (Top 40 German companies in terms of market capitalization) and came across the following statement, which i try to prove with the help of Dax.

"60% of the best days in terms of yield return occur within the top 10 worst days of the year."

This basically means that a lot of good days in terms of return usually occur within two weeks of bad days.

I've already calculated the top10 best and top10 worst days of the year in terms of return, which looks like this:

Plotting this onto a chart for a selected year of 2008 give the following result, which supports the statement, since the points are really close to eachother:

Now what i want to do with DAX (problem):

1) Go to the first bad date of the year which is the 21st of January.

2) if a bad date follows, go to the next bad date

(in our case the 21st of January is followed by the 23rd of January, so we go to the 23rd of January)

3) Otherwise, if a bad date is followed by a good date, then calculate the datediff in Days, if it is within 14 days, return 1

4) calculate the sums of one's in the column

5) calculate the sums of the column divided by the number of good dates in the year

If I am not mistaken, the column should return a value of 9 (9*1).

So the statement would be true, because 90% of the best days happen within 14 days of the worst days.

"60% of the best days in terms of yield return occur within the top 10 worst days of the year."

I hope you get my point and understand my measure. Thank you for any help.

Kind regards

Hendrik

EDIT: 

You can use all the DAX you know, but I think we need sth like: Calculate, Calculatetable, Datediff, Divide and some Filter functions I am not aware of^^

[johnt75 · Accepted solution]

Try something like

Good days near bad days =
VAR RankingTable =
    SELECTCOLUMNS ( 'Table', 'Table'[Date], 'Table'[Top10 Best Worst] )
VAR Result =
    SUMX (
        RankingTable,
        VAR CurrentDate = 'Table'[Date]
        VAR CurrentRanking = 'Table'[Top10 Best Worst]
        RETURN
            IF (
                CurrentRanking = 1,
                VAR PrevBadDate =
                    CALCULATE (
                        MAXX ( RankingTable, 'Table'[Date] ),
                        'Table'[Date] < CurrentDate,
                        'Table'[Top10 Best Worst] = 2
                    )
                RETURN
                    IF ( DATEDIFF ( PrevBadDate, CurrentDate, DAY ) <= 14, 1 )
            )
    )
RETURN
    Result