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
Icon for Super User rankSuper 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!