Forum Discussion

Birthe's avatar
Birthe
New Member
3 years ago
Solved

Selectedvalue and calculated columns

I have been struggling all day now. Can someone please help me?

To simplify, I have a table (+1000 records) with contracts, all having a start and an end date:

 

ContractnrContractFrom (date)ContractUntil (date)
101/01/201005/06/2023
205/04/202304/03/2028

 

I also have a date table 'Date'[date] , linked to other significant information.

As a result, I want to select one of the dates in my date table, and show how many contracts are valid for that given date.

 

As a first step, I was trying the following but it does not work

I wanted an extra column in the contracts table above with formula: 

 

DateBetween = IF(
    AND (
        'Contracts'[ContractFrom] <= SELECTEDVALUE('Date'[Date]),
        'Contracts'[ContractUntil] >= SELECTEDVALUE('Date'[Date])
    ),
    "Yes",
    "No")

 

This does not give the expected result. Can somebody help me? 

Thank you very much! Birthe

 
  • ERD's avatar
    ERD
    3 years ago

    Birthe ,

    You can try this measure:

    DateBetween =
VAR _from =
    SELECTEDVALUE ( 'Table'[ContractFrom] )
VAR _to =
    SELECTEDVALUE ( 'Table'[ContractUntil)] )
VAR _date =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    IF ( _date >= _from && _date <= _to, 1, 0 )

2 Replies

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Birthe ,

    You can try this measure:

    DateBetween =
VAR _from =
    SELECTEDVALUE ( 'Table'[ContractFrom] )
VAR _to =
    SELECTEDVALUE ( 'Table'[ContractUntil)] )
VAR _date =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    IF ( _date >= _from && _date <= _to, 1, 0 )