Forum Discussion

ArchieEric's avatar
ArchieEric
Frequent Visitor
1 year ago
Solved

Selectedvalue date between two dates

Hi
I would like to make a column on my table that shows whether the selected value table from a Date table is between twos date fields on the main table.
 
There is no relationship between the date table and the main table.
 
My Dax code currently is.
 
SelectedMasters =
VAR selectedmonth =
    selectedvalue('Months'[Month Year])
RETURN
    if(
        'ContinuousService'[ContractStart] <= selectedmonth &&
        'ContinuousService'[ContractEnd] >= selectedmonth
        , 1, 0)
 
which returns nothing, no 1s and no 0s.
 
Thanks in advance
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi ArchieEric 

    Please try the following Measure:

    Measure = IF(SELECTEDVALUE(CalendarTable[Date]) >= SELECTEDVALUE('Table'[Start]) && SELECTEDVALUE(CalendarTable[Date]) <= SELECTEDVALUE('Table'[End]) , 1 , 0)

     

    Count rows:

    Count = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Measure]=1))

     

    By using measure, you can dynamically calculate the total number of rows based on the different dates you choose.

    Result:

     

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi ArchieEric  It looks like your DAX code isn’t returning the expected results because the SELECTEDVALUE function might not be working as intended without a relationship between the tables. You can try this code:

    SelectedMasters = 
VAR selectedmonth = SELECTEDVALUE('Months'[Month Year])
RETURN
    IF (
        NOT ISBLANK(selectedmonth) &&
        'ContinuousService'[ContractStart] <= selectedmonth &&
        'ContinuousService'[ContractEnd] >= selectedmonth,
        1,
        0
    )

     

    It will  return 0 if selectedmonth is blank. I would suggest try develop relationship using virtual relationship function TREATAS if possible.

     


    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

    Best Regards,
    Shahariar Hafiz

  • ArchieEric's avatar
    ArchieEric
    Frequent Visitor
    1 year ago

    Im nut sure how i build a relationship between the two tables, the 'Months'[Month Year] field doesnt directly relate to any field in the 'ContinuousService' table

     

    the 'Months'[Month Year] field is a date and i want to pick rows from the 'ContinuousService' table when the selected [Month Year] field is between the [ContractStart] field and the [ContractEnd] Field

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ArchieEric

    Based on your needs, I have created the following table.

    CalendarTabe:(from 1/1/2010 to 10/29/2024)

    CalendarTable = CALENDAR(DATE(2010,1,1),DATE(2024,10,29))

     

    DateTable:

     

    Then use the following Dax:

    Measure = 
VAR _select_date = SELECTEDVALUE('Table'[Date])
VAR _max_date = MAX('CalendarTable'[Date])
VAR _min_date = MIN('CalendarTable'[Date])

RETURN
IF(_select_date >= _min_date && _select_date <= _max_date ,1,0)

     

     

    If you choose date from 1/1/2010 to 10/29/2024

     

    If you choose date from 1/26/2018 to 10/29/2024

     

     

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ArchieEric's avatar
      ArchieEric
      Frequent Visitor
      1 year ago

      I think I need a column rather than a measure

       

       

      Id like a 4th column on the table to say whether the selected month is between the ContractStartMonth and ContractEndMonth and id like to assess it row by row

       

      if there are 100 rows id like a card to say on 50 of them the selected month falls between the dates

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi ArchieEric 

        Are you only considering the month, or do you need to take the year into account as well?


        Please provide your sample data in a copyable format, rather than a screenshot.

         

         

         

         

        Best Regards,

        Jayleny

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ArchieEric 

    Please try the following Measure:

    Measure = IF(SELECTEDVALUE(CalendarTable[Date]) >= SELECTEDVALUE('Table'[Start]) && SELECTEDVALUE(CalendarTable[Date]) <= SELECTEDVALUE('Table'[End]) , 1 , 0)

     

    Count rows:

    Count = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Measure]=1))

     

    By using measure, you can dynamically calculate the total number of rows based on the different dates you choose.

    Result:

     

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ArchieEric's avatar
      ArchieEric
      Frequent Visitor
      1 year ago

      Excellent thank you

       

      seemed to be the extra use of SELECTEDVALUE on the main table field that did it?