Filtered min function

[mikaro]

Hello!

My table consists of transactions where every row is either a negative or positive transaction ( i.e. profit or loss) and other info related to them.

The thing i'd like to be able to calculate is date difference between a positive transaction and the first negative transaction from that row.

So first row should be 2.1.2016, 2nd a blank, 3rd 6.1.2016, 4th 6.1.2016 and so on. I've tried all kinds of dax command compinations with min, earlier, filter etc. to get a min date from neg_date for each row so that the returned date is greater than the date but i haven't been able to get it right.  I also tried to use only one supplier in the table but it didnt help. Am i trying to do it the wrong way or am I just missing something here? All help is much appreciated!

[v-ljerr-msft · Accepted solution]

Hi @mikaro,

According to your description, you should be able to follow steps below to get your expected result.

I assume you have a table called "Table1" like below.

1. Add an Index Column in Query Editor.

2. Use the formula below to create a calculate column to get the first negative date from current row for each row.

FirstNegativeDate = 
VAR i = Table1[Index]
RETURN
    IF (
        Table1[Value] < 0,
        BLANK (),
        CALCULATE (
            MIN ( Table1[Date] ),
            FILTER ( ALL ( Table1 ), Table1[Index] > i && Table1[Value] < 0 )
        )
    )

3. Then you should be able to calculate the difference between the positive transaction and the first negative transaction from current row.

DateDifference = 
IF (
    Table1[FirstNegativeDate] <> BLANK (),
    DATEDIFF ( Table1[Date], Table1[FirstNegativeDate], DAY )
)

Here is the sample pbix file for your reference.

Regards

[mikaro]

Thank you @v-ljerr-msft your solution was exactly what i was trying to achieve! Didn't know that you could write functions with variables like that in Power BI

[Baskar]

Do u have anyother column in that table like Id etc.