Forum Discussion

nelmade's avatar
nelmade
New Member
3 years ago

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

  • 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)