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

Hello,

 

Tried all 3 solutions without success. Managed to get the dates with my initial formula, with all colums (Prod A & B, Forecast) transformed into measures

 

Maybe you know how to get only one date from the result?

 

First date=
IF(OR([Calc. Sum Production A]>=[Forecast],
[Calc. Sum Production B]>=[Forecast]),
FIRSTDATE(Table1[Date]),"")

 

So now the result looks like that in Power BI - so I need to see only the first date (tried filtering blanks did not work)

 

 

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

I guess you could make a measure from your calculated column with min. This would return The earliest date or on this case of the Blank values cause issues in The min measure consider using If and Make a condition ignoring the blanks.