Forum Discussion
Anonymous
6 years agoNot applicable
Count Jobs by Status Over Time
I have data similar to the below: ID Created Converted Cancelled 1 1/1/2020 2 1/1/2020 1/15/2020 3 1/1/2020 1/15/2020 2/15/2020 4 1/1/2020 1/15/2020 Jobs ca...
- Anonymous6 years ago
amitchandak -- I appreciate all of your help! Below is the solution I ended up implementing...
I'm using the following formulas to count opportunities. The first counts jobs if they where created in that peorid, while the second does a cumulative count. This helps show a month to month as well as an overview:
Opportunity = CALCULATE( COUNT('Data'[ID]) ,FILTER( 'Data' , 'Data'[Created] <= MAX('Date'[Date]) && ( ISBLANK('Data'[Converted] ) || 'Data'[Converted] > MAX('Date'[Date]) ) ) ,CROSSFILTER( 'Data'[Created] ,'Date'[Date] ,None ) )Opportunity (Cumulative) = CALCULATE( COUNTX( FILTER( 'Data' ,'Data'[Created] <= MAX('Date'[Date]) && ( ISBLANK('Data'[Converted]) || 'Data'[Converted] > MAX('Date'[Date]) ) ) ,'Data'[ID] ) ,CROSSFILTER( 'Data'[Created] ,'Date'[Date] ,None ) )I also applied a relationship between 'Date'[Date] and 'Data'[Created] to allow proper filtering.
Thanks again!
Ashish_Mathur
6 years agoSuper User