Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 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!

amitchandak's avatar
amitchandak
Super User
6 years ago

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-trend/ba-p/882970

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 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!