Date when value is negative

[iiomarioii]

Hi guys, 

this is my table: 

Table1:

Date	Item number	Quantity (Measure)
01.01.2020	1	10
02.01.2020	1	5
03.01.2020	1	0
04.01.2020	1	-5
01.01.2020	2	12
02.01.2020	2	3
03.01.2020	2	-2
04.01.2020	2	-5

Quantitiy = CALCULATE (

SUM ( Inventory[Quantity] ),

FILTER (ALL (Date), 'Date'[Date] <= MAX ( 'Date'[Date])))

I have different items with decreasing values and I need a table showing me the date of the different items, when the value is positive (>0) for the last time. 

So for example: 

Table2:

Item number	Date
1	03.01.2020
2	02.01.2020

Any ideas? 🙂 

Thank you! 

[Fowmy]

@iiomarioii 

Try this as new column:

Positive = 
IF(
    [Date] = 
    CALCULATE(
        MAX(Table1[Date]),
        ALLEXCEPT(Table1,Table1[Item number]),
        Table1[Quantity]>0
    ), 
    [Date],
    BLANK()
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[iiomarioii]

@Fowmy 

Positive =

IF(

[Date] =

CALCULATE(

MAX('Date'[Date]),

ALLEXCEPT(Table1,Table1[item number]),

[Quantity]>0

),

[Date],

BLANK()

)  

Thank you! I get this error: 

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[Fowmy]

@iiomarioii 

I added it as a column and it can be used as a measure as well. You can add the new column as a filter in Visual filter and set it to  <>=Blank to show only those records that meet the condition.

Check the File: https://1drv.ms/u/s!AmoScH5srsIYgYIMjN8BuD99S0yuAA?e=XvlDMf

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[iiomarioii]

@Fowmy 

I ve tried your approach. Im still getting this error, when I use the date column fromthe date table: 

When using the date column in the table with the quantities, I'm getting this error: 

Thank you for your help.

[Fowmy]

@iiomarioii 

Use this measure if you have calendar Table:

Positive Calendar = 
VAR _Date = MAX('Calendar'[Date])

RETURN

IF( _Date = 
    CALCULATE( 
        MAX(Table1[Date]),
        ALLEXCEPT(Table1,Table1[Item number]),
    Table1[Quantity]>0
    ),
    _Date,
    BLANK()
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[iiomarioii]

@Fowmy  Thank you

I'm still getting this error: 

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[V-lianl-msft · Accepted solution]

Hi @iiomarioii,

Try add filter to the formula.

positive =
IF (
    'Gesamt2'[Date]
        = CALCULATE (
            MAX ( 'Date'[Date] ),
            FILTER ( ALLEXCEPT ( Tablle1, 'Tablle1'[Artikelnummer] ), [total] >= 0 )
        ),
    'Gesamt2'[Date],
    BLANK ()
)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V-lianl-msft]

Hi @iiomarioii ,

Is this problem sloved?
If not, please let me know.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@iiomarioii 

Could you share a sample file for me to check how you are applying the measure?

Thanks

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Greg_Deckler]

@iiomarioii  - Seems like you want a variation on Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

[iiomarioii]

@Greg_Deckler 

Thank you!

I tried to implement the formula "Loopup min" but unfortunately I am not able to do it that way because my Quantity column is a measure. It's not part of table 1

[Greg_Deckler]

@iiomarioii - Right, so what you will need to do is to use SUMMARIZE or ADDCOLUMNS or a combination of the two to recreate your table as a variable within your DAX code. You can then FILTER across that table to find the rows where your measure is greater than zero. Then you get the MINX of that. Some examples of how to do it can be found here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318

https://community.powerbi.com/t5/Quick-Measures-Gallery/TRIMMEAN/m-p/1074075#M504

https://community.powerbi.com/t5/Quick-Measures-Gallery/While-Loop/m-p/637535#M320