Forum Discussion
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 | 3/16/2023 | 3/16/2023 | 3/16/2023 |
3/16/2023 | 3/16/2023 | 3/16/2023 | 3/16/2023 |
10/3/2022 | 10/3/2022 | 3/17/2023 | 3/18/2023 |
2/28/2023 | 2/28/2023 | 2/28/2023 | 3/1/2023 |
3/10/2023 | 3/10/2023 | 3/10/2023 | 3/14/2023 |
I have 2 field parameters: start_date and end_date:
Start_date = {("DE date", NAMEOF('Table'[DE_Date]), 0), ("OE date", NAMEOF('Table'[OE_Date]), 1)}
End_date = {("PV date", NAMEOF('Table'[PV_Date]), 0), ("Sold_Date", NAMEOF('Tables'[Sold_Date]), 1)}
I want to create a visual showing the datediff between the start and end date depending on the date chosen for each parameter.
Can someone help me with that?
Thank you
1 Reply
- MarkLafSuper User
I 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)