Forum Discussion

pe2950's avatar
pe2950
Icon for Helper I rankHelper I
3 years ago

Calculate Close Ratio Across 2 Dates?

I'm trying to calculate and display the close/conversion ratio across employees and having an issue with the way the dates are handled in the dataset.

 

Data Schema

created_at | closed_date | Status | Employee

 

When the record is created in CRM the created_at is populated, the closed_date is NULL. Once the employee closes the record (let's say win) it will populate the closed_date timestamp and update the status accordingly.

 

I have created 2 realtionships to my dateKey table (to the created, and closed dates), and set the created_at as active, with the filter direction single.

 

I am using 3 measures to calculate the CR. The CR is defined as Won-Closed Records / Total Records, over the same period of time.

 

Created Deals = 
CALCULATE (COUNTROWS(fs_deals),  USERELATIONSHIP(fs_deals[created_at], DateKey[Date] ) )

 

Won Deals = 
CALCULATE (countrows(fs_deals), fs_deals[deal_stage_id]="3000149092", USERELATIONSHIP (  fs_deals[closed_date],  DateKey[Date]) )

 

The measures work when they are used by themselves, for example if i drop "Won Deals" into a matrix and set the filter to closed_at = This year; i get the expected result. The same can be said for "Created Deals", when i set the created_at filter to this year i get the expected result.

 

My issue, is when I drop them both into a matrix, and then sent the "created_at" to this year, it applies the filter to the "Won Deals" measure.

 

  • Is it possible to have a filter only apply to the measure in the matrix?
  • Since i have USEREALTIONSHIP() in the measure, why is "Won Deals" still filtering by created_at?
  • Is there a better way to do this? 
Need Help

1 Reply

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi, pe2950,

     

    you are using the date field, created_at, directly from the fact table. Try to change your filter/slicer to use 'Datekey'[date] instead, and rewrite [Won deals] to 

    Won deals =
VAR _date =
    CALCULATE ( SELECTEDVALUE ( DimDate[Date] ) )
RETURN
    CALCULATE (
        COUNTROWS ( FactRecords ),
        FILTER (
            ALLEXCEPT ( FactRecords, FactRecords[employee] ),
            FactRecords[status] = "closed"
                && FactRecords[closed_at] = _date
        )
    )

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.