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

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's avatar
Fowmy
Super User
6 years ago

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's avatar
    iiomarioii
    Helper II
    6 years ago

    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.

    • Fowmy's avatar
      Fowmy
      Super User
      6 years ago

      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

    • V-lianl-msft's avatar
      V-lianl-msft
      Community Support
      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.

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        6 years ago

        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.