Running total percentage for last 30 days with inactive relationship not returning results

[jfbonterra]

My formula is to count the number of records over a given period of time divided by a similar figure to get a point in time percentage. No errors with the formula but I don't return any results when I try to use an inactive relationship. Confirmed both fields in the inactive relationship are set up as one to many and both are Date types. 

Close Rate =

    var ClosedWon =

        CALCULATE ( COUNTROWS ( 'Opportunities' ),

            USERELATIONSHIP ( Opportunities[Close Date], 'Calendar'[Date] ),

            FILTER ( Opportunities, Opportunities[Deal Stage] in {"Closed won" } ),

            DATESINPERIOD ( 'Calendar'[Date], MAX ('Calendar'[Date]), -30, DAY ))

    var Closed = CALCULATE ( COUNTROWS ( 'Opportunities' ),

            USERELATIONSHIP ( Opportunities[Close Date], 'Calendar'[Date] ),

            FILTER ( Opportunities, Opportunities[Deal Stage] in {"Closed lost", "Closed won", "Closed – Disqualified (BDR)" }  ),

            DATESINPERIOD ( 'Calendar'[Date], MAX ('Calendar'[Date]), -30, DAY ) )

    var WinRate = DIVIDE ( ClosedWon, Closed )

    return WinRate

Any ideas why this isn't working?

Thanks!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but try somethinglike below whether it suits your requirement.

Close Rate =
VAR ClosedWon =
    COUNTROWS (
        FILTER (
            ALL ( 'Opportunities' ),
            Opportunities[Deal Stage]
                IN { "Closed won" }
                    && Opportunities[Close Date] <= MAX ( 'Calendar'[Date] )
                    && Opportunities[Close Date]
                        >= MAX ( 'Calendar'[Date] ) - 30
        )
    )
VAR Closed =
    COUNTROWS (
        FILTER (
            ALL ( 'Opportunities' ),
            Opportunities[Deal Stage]
                IN { "Closed lost", "Closed won", "Closed – Disqualified (BDR)" }
                    && Opportunities[Close Date] <= MAX ( 'Calendar'[Date] )
                    && Opportunities[Close Date]
                        >= MAX ( 'Calendar'[Date] ) - 30
        )
    )
VAR WinRate =
    DIVIDE ( ClosedWon, Closed )
RETURN
    WinRate

[jfbonterra]

Thanks! Adding the ALL filter seemed to do the trick