Forum Discussion
Display first date based on multiple if statements
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] )
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)
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]
)
Dear
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,