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]
)

     

     

queryuser's avatar
queryuser
Icon for Helper I rankHelper I
4 years ago

Dear  smpa01,

 

The calculation worked well. Yet how would you display the first date as an attribute if for example there would an additional column saying internal or external demand for each of those rows.

 

So that the result is now not the date but the text of the additional column indicating if this is (internal or external) date. 

 

Many thanks in advance!

 

Best regarads,

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

queryuser  please create a new thread, provide sample data and expected output.