cancel
Showing results for
Did you mean:
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.

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!

1 ACCEPTED SOLUTION

Accepted Solutions
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.

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

3 REPLIES 3
Highlighted
Super Contributor

## Re: Filtered min function

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

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.

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

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

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 113 members 1,588 guests
Recent signins: