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
Baskar Super Contributor
Super Contributor

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 113 members 1,588 guests
Please welcome our newest community members: