Display first date based on multiple if statements

[queryuser]

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!

[queryuser]

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)

[smpa01 · Accepted solution]

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

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]

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

[smpa01]

@queryuser did you have a chance to look into this?

[ValtteriN]

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.

[ValtteriN]

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!

[rbriga]

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

[amitchandak]

@queryuser ,

Minx(filter(Table, Table[Forecast] < Table[ProductionA] ||Table[Forecast] < Table[ProductionB]), Table[Date])