Forum Discussion

iiomarioii's avatar
iiomarioii
Helper II
6 years ago
Solved

Date when value is negative

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...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

iiomarioii's avatar
iiomarioii
Helper II
6 years ago

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's avatar
Greg_Deckler
Community Champion
6 years ago

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