Forum Discussion

mikaro's avatar
mikaro
Frequent Visitor
9 years ago
Solved

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!

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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.:smileyhappy:

     

    Regards

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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.:smileyhappy:

     

    Regards

    • mikaro's avatar
      mikaro
      Frequent Visitor
      9 years ago

      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 :smileyvery-happy:

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

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