Forum Discussion

queryuser's avatar
queryuser
Helper I
4 years ago
Solved

Display first date based on multiple if statements

Hello everyone,   Question: How do I display the first Date using DAX when either Production A or Production B exceeds the values in the Forecast culumn?   Thank you!      
  • smpa01's avatar
    smpa01
    4 years ago

    queryuser you can use this measure

    Measure =
MINX (
    CALCULATETABLE (
        VALUES ( 'Table'[Date] ),
        FILTER (
            'Table',
            VAR _prodA =
                CALCULATE ( SUM ( 'Table'[Prod A] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
            VAR _prodB =
                CALCULATE ( SUM ( 'Table'[Prod B] ), ALLEXCEPT ( 'Table', 'Table'[Prod B] ) )
            VAR _forecast =
                CALCULATE ( SUM ( 'Table'[Forecast] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
            RETURN
                _prodA > _forecast
                    || _prodB > _forecast
        )
    ),
    'Table'[Date]
)

     

     

rbriga's avatar
rbriga
Impactful Individual
4 years ago

First Exceed =
CALCULATE(
MIN( 'Table1'[Date] ),
FILTER(
Table1,
OR(
Table1 [Production A] > Table1 [Forecast],
Table1 [Production B] > Table1 [Forecast]
)
)
)