cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mikaro Frequent Visitor
Frequent Visitor

Filtered min function

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.

table.PNG

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.

bitable.PNG

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!

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Filtered min function

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.

 

t1.PNG

 

1. Add an Index Column in Query Editor.

 

index1.PNG

i2.PNG

 

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 )
        )
    )

c1.PNG

 

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 )
)

c2.PNG

 

Here is the sample pbix file for your reference.Smiley Happy

 

Regards

3 REPLIES 3
Highlighted
Super User
Super User

Re: Filtered min function

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

v-ljerr-msft Super Contributor
Super Contributor

Re: Filtered min function

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.

 

t1.PNG

 

1. Add an Index Column in Query Editor.

 

index1.PNG

i2.PNG

 

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 )
        )
    )

c1.PNG

 

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 )
)

c2.PNG

 

Here is the sample pbix file for your reference.Smiley Happy

 

Regards

mikaro Frequent Visitor
Frequent Visitor

Re: Filtered min function

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 Smiley Very Happy