cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

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

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!
1 ACCEPTED SOLUTION
Super User

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
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Super User

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
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Thanks! Adding the ALL filter seemed to do the trick

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors