Forum Discussion
nelmade
3 years agoNew Member
Using a parameter values in a calculated field
My initial data is : OE date DE date PV date Sold date 3/15/2023 3/15/2023 3/15/2023 3/15/2023 1/25/2023 1/25/2023 3/9/2023 3/14/2023 3/16/2023 ...
MarkLaf
Super User
3 years agoI haven't used field parameters like this before, and I suspect there is a smarter way to do this, but the following seems to be working with your test data at least in a vanilla table visual:
Date_Start =
VAR _minDE = MIN( 'Table'[DE date] )
VAR _minOE = MIN( 'Table'[OE date] )
VAR _minAll = MIN( _minDE, _minOE )
VAR _selectedDE = CALCULATE(
NOT ISEMPTY( Start_date ),
FILTER( Start_date, NAMEOF( 'Table'[DE date] ) = Start_date[Start_date Fields] )
)
VAR _selectedOE = CALCULATE(
NOT ISEMPTY( Start_date ),
FILTER( Start_date, NAMEOF( 'Table'[OE date] ) = Start_date[Start_date Fields] )
)
RETURN
SWITCH(
TRUE(),
_selectedDE && _selectedOE, _minAll,
_selectedDE, _minDE,
_selectedOE, _minOE
)
Date_End =
VAR _maxPV = MAX( 'Table'[PV date] )
VAR _maxSold = MAX( 'Table'[Sold date] )
VAR _maxAll = MAX( _maxPV, _maxSold )
VAR _selectedPV = CALCULATE(
NOT ISEMPTY( End_date ),
FILTER( End_date, NAMEOF( 'Table'[PV date] ) = End_date[End_date Fields] )
)
VAR _selectedSold = CALCULATE(
NOT ISEMPTY( End_date ),
FILTER( End_date, NAMEOF( 'Table'[Sold date] ) = End_date[End_date Fields] )
)
RETURN
SWITCH(
TRUE(),
_selectedPV && _selectedSold, _maxAll,
_selectedPV, _maxPV,
_selectedSold, _maxSold
)
Date_Diff = INT( [Date_End] - [Date_Start] )
Result (note that I made OE date = DE date - 1 as it was harder to verify things were working correctly when they were equal)