how to get total blank by month

[fstupot]

hi guys,

i have historical data showing when a job is raised and when it has been completed.

when a job is still incomplete it has a blank cell under "completed date".

i want to show a monthly breakdown of how many jobs were not completed (blank) by month.

any help appreciated 

lucy x

data as per below

job_entry_date	actual_comp_date	job_easting	job_northing	priority_code	job_type_name
03/11/2020		403352.8	353284	C3	Drain - Camera / Cutter Required
03/11/2020		380042.2	313845.5	C3Q	DRAIN Ditch Clearing
02/11/2020		381980.2	346457	C3	Drain - Camera / Cutter Required
02/11/2020		404497	307841	C3	Drain - Camera / Cutter Required
02/11/2020		397532	312135.8	C3	Drain - Camera / Cutter Required
02/11/2020		385850.9	328875.4	C3	Drain - Camera / Cutter Required
02/11/2020		420270	321638	C3	Drain - Camera / Cutter Required
02/11/2020		386779.8	322230.2	C3	Drain - Camera / Cutter Required
02/11/2020	03/11/2020	415468.3	306484.4	C3M	Drain - Camera / Cutter Required
02/11/2020	03/11/2020	399324	313824	C3	Drain - Camera / Cutter Required

[fstupot]

hi guys x

i want to show historically over a 12 month rolling perioid how many jobs i have outstanding

i have the job entry date, the job completed date (if completed)

if there is no completed date then that job is obviously outstanding. so i effectively want to show total blanks for previous months.

i cant seem to get my head round it and a previous solution hasnt worked.....the data is below.

thanks guys x

job_entry_date	actual_comp_date	job_easting	job_northing
03/11/2020		403352.8	353284
03/11/2020		380042.2	313845.5
02/11/2020	05/11/2020	381980.2	346457

[PaulDBrown]

@fstupot 

Is this the same question you posted in this thread?

how to get total blank by month 

If so, please don't duplicate the question in different threads (keeping a single thread helps others looking for a similar solution and avoids duplicating answers etc...)

[amitchandak]

@fstupot , Not very clear. But refer to my HR blog . The current epmoyee seems the same has an open job .

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

https://www.youtube.com/watch?v=e6Y-l_JtCq4

[PaulDBrown · Accepted solution]

@fstupot 

Try this measure:

Not Completed = COUNTROWS(
                    FILTER('DataTable', 
                        ISBLANK('DataTable'[actual_comp_date])))

FYI, this is how I have the model set up:

[fstupot]

sorry but that doesnt seem to work. im sure i am doing something wrong. i have created a date table with mm/yyyy and linked job start date and actual complete data to it.

yet when i try to show something on a line chart i get nothing

i dont understand why showing outstanding workstack is so hard!

[amitchandak]

@fstupot ,

Join Job_entry with a date table and plot month wise data to filter for month.

This will give jobs not completed

calculate(countrows(Table), filter(Table, isblank([actual_comp_date])))

or refer my HR blog which can help find open job per month based what is still not closed

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

or

https://www.youtube.com/watch?v=e6Y-l_JtCq4