Help with Date Filter and Status

[KelliKnitsAlot]

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.status IN ('O', 'R')
            OR (rm.status = 'C' AND RM.ACT_DATE_CLOSED > :RDate))

I have tried two date filters and a status filter, but it has not worked. Any assistance would be helpful.

Thanks!

[Greg_Deckler]

I believe you want the Open Tickets quick measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

[KelliKnitsAlot]

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

[Icey]

Hi @KelliKnitsAlot ,

Please give me some data sample.

Best Regards,

Icey

[KelliKnitsAlot]

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]

Hi @KelliKnitsAlot ,

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.

[KelliKnitsAlot]

Thanks Icey. This almost works, but we also need to consider the issued date. For example, if someone selects an earlier date, the number should also change.   See below. This count should be 0 since none of the report were issued in January 1, 2017.

[Icey · Accepted solution]

Hi @KelliKnitsAlot ,

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.

[KelliKnitsAlot]

Thanks @Icey.  I got the number I was looking for, but when I created a relationship between the new CALENDAR table and my data table, it did not work. I need to be able to have a table of data results that users can export for reporting. Should the reltionship be on the ISSUED DATE or the CLOSED DATE?

[Icey]

Hi @KelliKnitsAlot ,

The new table "Calendar" which is a slicer table need to be independent of other tables. If it is not independent of other tables, it will be affected by them when selected. The effect is called interaction.

Best Regards,

Icey

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