returning the count and average age of open jobs

[Anonymous]

hi so ive hit a wall with this one,

bassically ive the below table format 

open date	closed date	Age days
01/02/2022	01/03/2022	30
01/02/2022	01/03/2022	30
01/02/2022	01/03/2022	30
01/02/2022	01/03/2022	30
01/02/2022	01/03/2022	30
08/02/2022	01/03/2022	21
08/02/2022	01/03/2022	21
08/02/2022	08/04/2022	60
08/02/2022	08/04/2022	60
08/02/2022	08/04/2022	60

what im tring to get is a graph that had date along the bottom and then for each date plotted it gives a count for each job that was open during that date aka for 02/02/2022 count would be 5, id also then need a average age for the jobs that are open on that same period for the same date "09/02/2022" the average age should be 27.43

ive tried using a mesure similar to this but it sinst getting the expected results 

var active = CALCULATE(COUNTROWS('Job table'), // Create Variable and count rows of Table1

ALL('Date Table'[Date]),'Date Table'[Date]<=maxdate, // Include all rows in date table up to the last date of the filter

ISBLANK('Job table'[Closed date]) || // Exclude rows up with blank Close Date (|| = OR)

'Job table'[Closed date] >=maxdate )

RETURN 

active

any help appreciated

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

it took me a bit to understand what you ment, i is a solution but i dont see how ill be able to build off of that to get the average age og the jobs 

[Ashish_Mathur]

Hi,

For any date, how should average days be calculated?  Please explain.

[Anonymous]

it should take a date and then find all jobs with a opendate erlier and a close date later (meaning it was open at that time) then average the age of all jobs that meet that criteria.  rethinking the problem on fresh eyes im going to ask anew queswtion that a bit more detailed as i think this is more complex than i first thought thanks for you help!.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a measure as below to get the count of active job:

Active job Count =
VAR __DATE =
    MAX ( 'Date Table'[Date] )
RETURN
    SUMX (
        'Job table',
        IF (
            'Job table'[Open Date] <= __DATE
                && OR (
                    'Job table'[Closed date] >= __DATE,
                    ISBLANK ( 'Job table'[Closed date] )
                ),
            1,
            BLANK ()
        )
    )

And you can refer the following links to get it:

1. Get the count of active xxx

How Many Staff Do We Currently Have – Multiple Dates Logic In Power BI Using DAX

Create inactive relationships

Create measure

Create visuals

Count Amount of Active Employees by period

Counting “Active” Rows in a Time Period: Guest Post from Chris Campbell

2. Get the average of age

Calculating Ave Age of Active Employees

Average Age = 
CALCULATE (
    AVERAGE(AllStaff[Age]);
    FILTER (
        AllStaff;
        NOT(AllStaff[Last Date Worked] = BLANK())
    )
)

If the above ones can't help you get the desired result, please provide some raw data in the table "Job table" (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards