cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
G0ggs
Helper I
Helper I

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

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 nameComplexity IndustryTool Start dateFinish date

 

Thanks,

Gordon 

1 ACCEPTED SOLUTION
v-yuaj-msft
Community Support
Community Support

Hi @G0ggs ,

 

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

Here is my test table.

v-yuaj-msft_0-1620374781801.png

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:

050703.gif

 

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.

 

View solution in original post

6 REPLIES 6
v-yuaj-msft
Community Support
Community Support

Hi @G0ggs ,

 

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

Here is my test table.

v-yuaj-msft_0-1620374781801.png

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:

050703.gif

 

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.

 

View solution in original post

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
Community Support
Community Support

Hi @G0ggs ,

 

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

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

v-yuaj-msft_0-1620615203377.png

2. establish a relationship between two tables.

v-yuaj-msft_1-1620615248122.png

3. create a "KPI" visual.

Result:

051001.gif

Hope that's what you were looking for.

Best Regards,

Yuna

 

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_1-1620408985631.png

 

 

G0ggs_0-1620408913989.png

 

G0ggs
Helper I
Helper I

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

Thanks,

Gordon 

amitchandak
Super User IV
Super User IV

@G0ggs , this seems very similar to HR blog.

refer

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors