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.

       

       
      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi Anonymous ,

        Try this:

        1. Create a calendar table. It is independent of the other table.

        Calendar = CALENDARAUTO()

        2.  Create measures.

        IsAfterSelectDate =
VAR SelectDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR CurrentDate =
    MAX ( 'Table'[Date Closed] )
RETURN
    IF ( ISBLANK ( CurrentDate ), 1, IF ( CurrentDate > SelectDate, 1, 0 ) )
        Count of Open = SUMX('Table',[IsAfterSelectDate])

         

        PBIX file attached.

         

        Best Regards,

        Icey

         

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