Forum Discussion

torbenani's avatar
torbenani
Frequent Visitor
5 years ago
Solved

Comparing date isue

Hi 

hope you're able to help me with this issue.

In a table of service request, I want to list requsts, which has been closed (solutiondate) within a given date range.

the calendar table is link to the CreateDate of the request, so it won't work. 

I've this meassure. 

Closed in Selcted periond = if(SELECTEDVALUE(bi_Request[solutionDate]) < MAX('Calendar'[date]) && SELECTEDVALUE(bi_Request[solutionDate]) > CALCULATE(MIN('Calendar'[Date])) , "Yes", "No")

 

On every row the meassure is evaluated to "yes". What am I missing?

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi torbenani 

    From my test, you just need to build an unrelated Calendar Table, then your measure works well.

    I build a easy sample.

    Calendar Table:

    Calendar = CALENDARAUTO()

    You don't need to build a relationship between two tables.

    I try your measure. 

    Closed in Selcted periond = if(SELECTEDVALUE('Table'[Solution Date]) < MAX('Calendar'[date]) && SELECTEDVALUE('Table'[Solution Date]) > CALCULATE(MIN('Calendar'[Date])) , "Yes", "No")

    Result:

    Best Regards,

    Rico Zhou

     

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

     

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi torbenani ,

     

    For this you need to create an inactive relationship between the calendar table and the solutiondate column then you can make a simple measure using the USERELATIONSHIP formula, it should look something similar to this:

     

    Closed number =
IF (
    CALCULATE (
        COUNT ( bi_request[ID] ),
        USERELATIONSHIP ( bi_request[solutiondate], Calendar[Date] )
    ) > 0,
    "Yes",
    "No"
)

    Check result below and in attach file

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi torbenani 

    From my test, you just need to build an unrelated Calendar Table, then your measure works well.

    I build a easy sample.

    Calendar Table:

    Calendar = CALENDARAUTO()

    You don't need to build a relationship between two tables.

    I try your measure. 

    Closed in Selcted periond = if(SELECTEDVALUE('Table'[Solution Date]) < MAX('Calendar'[date]) && SELECTEDVALUE('Table'[Solution Date]) > CALCULATE(MIN('Calendar'[Date])) , "Yes", "No")

    Result:

    Best Regards,

    Rico Zhou

     

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