Forum Discussion

queryuser's avatar
queryuser
Icon for Helper I rankHelper 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]
)

     

     

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

Hi,

One easy way to achieve this is to use filtered calculated table as an intermediate step.

My test data (so here we want to get 3.12.2021):

Calculated table:
Here I use or to create a filtered table with all the cases where either A or B are greater than forecast

Tempdate = var forecast = max(GetDate[Forecast]) return
FILTER(GetDate,or((GetDate[A])>forecast,GetDate[B]>forecast))

Final measure:
Now I just need to get min of the date column in my calculated table.

Hope this helps and if it does consider accepting this as a solution!