write measure / dax to calculate number of rows for given date range

[powerbidu]

Order	Identifier	Buy/Sell	No.of exceptions
1-Oct	A	Buy	0
1-Oct	A	Sell	1
2-Oct	B	Buy	0
2-Oct	A	Sell	1
4-Oct	A	Sell	1
6-Oct	A	Buy	1
8-Oct	A	Buy	0
8-Oct	A	Buy	0
10-Oct	A	Sell	2

I have the trade data presented above. For the same identifier, I want to identify the number of trades that have an opposite direction occurring within three days prior to each trade. 

For example, the first trade line will flag 0 exceptions since there are no prior trades. The second trade line (a sell trade for A) will flag one exception because there is a buy trade for A on the same date (i.e. this is within three days prior). The fourth trade line (another sell trade for A) will also flag one exception. The logic is that three days before 2nd October is 29th September, so I would like to count all opposite trades (i.e. buy trades) for A during the preceding three-day period (i.e., 29th September to 2nd October). I would like to create a measure that produces results shown under 'No. of exceptions' column. 

Additional question (seems tough to execute): I know I had set the date range to be three days, but could anyone also advise on how I could approach this if I want to dynamically change the number of days? I am visualising a slicer where I could select the number of days (e.g. 1 to 10 days) and the number of exceptions will change accordingly.

Thank you.

[v-mdharahman]

Hi @powerbidu,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to calculate no of rows for some given data. As @AmiraBedh and @lbendlin both responded to your query, please go through the responses and mark the helpful reply as solution.

I would also take a moment to thank @AmiraBedh and @lbendlin, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[AmiraBedh · Accepted solution]

You need to create a calculated column if you are looking for a static solution :

No. of Exceptions = 
VAR CurrentDate = Trades[Order] 
VAR CurrentIdentifier = Trades[Identifier] 
VAR CurrentBuySell = Trades[Buy/Sell] 
VAR OppositeBuySell = IF(CurrentBuySell = "Buy", "Sell", "Buy") 
VAR StartDate = CurrentDate - 3  

VAR ExceptionCount = 

CALCULATE(
    COUNTROWS(Trades),
    FILTER(
        Trades,
        Trades[Identifier] = CurrentIdentifier &&  
        Trades[Buy/Sell] = OppositeBuySell &&  
        Trades[Order] >= StartDate && 
        Trades[Order] < CurrentDate  
    )
)
RETURN
IF(ISBLANK(ExceptionCount), 0, ExceptionCount)

If you want it dynamically, create a table that contains the number of days you want to use as a slicer :

DaysTable = GENERATESERIES(1, 10, 1)

Then another calculated column :

No. of Exceptions v2 = 
VAR Selection = SELECTEDVALUE(DaysTable[Value], 3) 
VAR CurrentDate = Trades[Order] 
VAR CurrentIdentifier = Trades[Identifier] 
VAR CurrentBuySell = Trades[Buy/Sell]
VAR OppositeBuySell = IF(CurrentBuySell = "Buy", "Sell", "Buy") 
VAR StartDate = CurrentDate - Selection
VAR ExceptionCount = 

CALCULATE(
    COUNTROWS(Trades),
    FILTER(
        Trades,
        Trades[Identifier] = CurrentIdentifier &&  
        Trades[Buy/Sell] = OppositeBuySell &&  
        Trades[Order] >= StartDate &&  
        Trades[Order] < CurrentDate  
    )
)
RETURN
IF(ISBLANK(ExceptionCount), 0, ExceptionCount)

[powerbidu]

Hi thank you so much! Your solution works (almost perfectly), except that the numbers of exceptions do not change when I click any other value on the slicer (the formula always runs based on the default value of '3' instead of the number marked in the slicer. Do you happen to know how to fix this?

[lbendlin]

The second trade line

Power BI does not guarantee any sort order.  You need to bring your own index

i.e. this is within three days prior

To do date math you need to provide correctly formatted dates.

To dynamically change the date range adjust the measure formula.