Looking to create column that counts number of jobs completed within each month

[G0ggs]

Hello,

I am looking to create a count of number of completed jobs and forecasted jobs within each month of the year based on the following columns and be able to filter by complexity, industry, tool type etc. Can you help me out with the formula please? 

Job name

Complexity

Industry

Tool

Start date

Finish date

Thanks,

Gordon 

[v-yuaj-msft · Accepted solution]

Hi @G0ggs ,

Based on your description, you could create some measures as follows.

Here is my test table.

1. create a calendar table

2. create a measure

completed jobs = 
CALCULATE (
    DISTINCTCOUNT ( 'Test'[Job name] ),
    ALLEXCEPT ( 'Test', Test[Complexity], Test[Industry], Test[Tool] ),
    FILTER (
        'Test',
        YEAR ( MAX ( 'Test'[Finish date] ) ) = YEAR ( MAX ( 'calendar'[Date] ) )
            && MONTH ( MAX ( 'Test'[Finish date] ) ) = MONTH ( MAX ( 'calendar'[Date] ) )
    )
)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[G0ggs]

Hi Yuna thanks for this! 

I was wondering if it also possible to create a target table for each tool type so when plotted you can see the completed jobs per month versus the target please?

Thanks

[v-yuaj-msft]

Hi @G0ggs ,

Based on your description, maybe you could do some steps as follow.

1. create a target table. Here is my test table.

2. establish a relationship between two tables.

3. create a "KPI" visual.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

[G0ggs]

Hello Yuaj I created a date table then linked many to 1 single but I get an error as shown below, not sure if I have set up the date correctly?

[G0ggs]

interesting! How did you create the columns in the date table it only shows 1 formula for the date column? 

Thanks,

Gordon 

[amitchandak]

@G0ggs , this seems very similar to HR blog.

refer

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