Count Jobs by Status Over Time

[Anonymous]

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 can have the following statuses: Opportunity / Order / Work Not Done. I need to count the number of jobs by status over time, being able to filter by the [Created] date.

An example of some questions may be "How many opportunities did we have on January 5th?" or "How many orders did we have in January 2020?"

The logic should compare the above dates to the timeline [date], applying the following logic:

Opportunity	If [Converted] = NULL then "Opportunity"
Work Not Done	If [Converted] <> NULL and [Cancelled] <> NULL then "Work Not Done"
Order	If [Converted] <> NULL and [Cancelled] = NULL then "Complete"

For example...

• Record 1 is an "Opportunity" from 1/1 onward
• Record 2 is an "Opportunity" from 1/1 to 1/14 and "Order" from 1/15 onward
• Record 3 is an "Opportunity" from 1/1 to 1/14, an "Order" from 1/15 to 2/14, and "Work Not Done" from 2/15 onward
• Record 4 is an "Opportunity" from 1/1 onward (a cancelled date doesn't mean a conversion can't happen later)

Thanks for any help!

[Ashish_Mathur]

Hi,

I have done some work in thi file.

Hope this helps.

[amitchandak]

Please find the link : https://www.dropbox.com/s/88esktvdjmrzlus/Workdone.pbix?dl=0

Opportunity = CALCULATE(COUNT(Sheet1[ID]),filter(Sheet1,(ISBLANK(Sheet1[Converted]) || Sheet1[Converted]>max('Date'[Date]) && Sheet1[Created] <=max('Date'[Date]) )))
Order = CALCULATE(COUNT(Sheet1[ID]),filter(Sheet1,(ISBLANK(Sheet1[Cancelled]) || Sheet1[Cancelled]>max('Date'[Date])) && Sheet1[Converted] <=max('Date'[Date]) && not(ISBLANK(Sheet1[Converted]))))
Work Not Done = CALCULATE(COUNT(Sheet1[ID]),FILTER(Sheet1,Sheet1[Cancelled]<=max('Date'[Date]) && not(ISBLANK(Sheet1[Cancelled])) && not(ISBLANK(Sheet1[Converted]))))

Also refer, if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

@amitchandak  -

This is exactly what I was looking for, thank you!

The only issue is filtering... When I filter by [Created] the graph's start and end date aren't trimmed, but the data is. However, if I filter by [Date] the graph is filtered, but the data is not. I've found that if I apply a relationship between [Created] & [Date], the measures seem to get thrown out of wack. I've tried making active and inactive. 

Any other tips? Nothing I've done seems to solve it.

@Ashish_Mathur -

I've actually used a similar solution to yours, however due to the amount of data we're working with, I think this may balloon the data size too much.

Thanks!

[amitchandak]

@Anonymous 

I did not join it with date table. Now if you want to join. create the joins and then you need to use userelation and crossfilter to make these work.

Now if you one active relation. Make all count as countx and move filters clause there and add the cross filter for the active join . Refer to my Employment blog.

My advice would keep all join inactive and then use the join using use relation to create measure based on created date.

In case I missed out on something you want; let me know.

refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous · Accepted solution]

@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!