Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with Date Filter and Status

I am trying to create a visual that shows the number of widgets open as of a certain date. The SQL we have used in the past to do this is:       AND rs.date_issued <= :RDate        AND (   rm.statu...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

    Try this:

    IsAfterSelectDate =
VAR SelectDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR CurrentClosedDate =
    MAX ( 'Table'[Date Closed] )
VAR CurrentIssuedDate =
    MAX ( 'Table'[Issued Date] )
RETURN
    IF (
        ISBLANK ( CurrentClosedDate )
            && SelectDate >= CurrentIssuedDate,
        1,
        IF ( CurrentClosedDate > SelectDate && SelectDate >= CurrentIssuedDate, 1, 0 )
    )

     

    Best Regards,

    Icey

     

    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
    6 years ago

    Thanks Greg! This is real close but the user needs to be able to select specifc dates in the visual. Any ideas?

    • Icey's avatar
      Icey
      Community Support
      6 years ago

      Hi Anonymous ,

      Please give me some data sample.

       

      Best Regards,

      Icey

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi there, Here is a small data example. Bascially, users want to be able to select a specific data filter and get a count of reports open at that specific date.  In the example below, we would expect a count of three open on 10. 1.2019.