Dax formula to find the average of each values between 2 dates

[Anonymous]

so Ive been trying to create a dax formular for som time that dose 2 things:

1)given a table with jobs that have open date and close dates, will return on each date of my already created date tabel the amount of jobs whos create date is earlier and closed date is later than the specified date.

ie. if my date table contained only one date being 3/1/2022 id get a 1 from my mesure 

open       |    closed 
1/1/2022->5/1/2022 

but if the date table was a weekly value and the next date to check was 10/1/2022 then it would return 1 then 0

if there were two jobs open over that period it would get a two 

job#1 -1/1/2022->5/1/2022 

job#2 -1/1/2022->6/1/2022 

this is needed to make a graph showing that the total number of jobs on a given day is slowly going down

the next step would be to get those same jobs and average the age between then from the start date.

meaning if it was open on the date being checked then find the time from creation to the date being cheked and average that age of jobs between every job open on that date being checked.

returning the mesure for a graph that will show the total average age of jobs along a timeline

open date	closed date
1/1/2022	1/12/2022
1/1/2022	1/11/2022
1/1/2022	1/10/2022
1/1/2022	1/9/2022
1/1/2022	1/8/2022
1/1/2022	1/7/2022
1/1/2022	1/6/2022
1/1/2022	1/5/2022
1/1/2022	1/4/2022

date table and expected results :

Date	mesure jobs open	average age
1/4/2022	9	4
1/5/2022	8	5
1/6/2022	7	6
1/7/2022	6	7
1/8/2022	5	8
1/9/2022	4	9
1/10/2022	3	10
1/11/2022	2	11
1/12/2022	1	12

i appreciate any help, if i didnt think the dax im currently trying to make work wasnt completly crap id inclued that as well

Thanks 

[Anonymous · Accepted solution]

Hi @Anonymous ,

I suggest you to try these code to create measures to achieve your goal.

mesure jobs open = 
CALCULATE (
    COUNT ( 'Table'[Job ID] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[open date] <= MAX ( 'Table'[closed date] )
            && 'Table'[closed date] >= MAX ( 'Table'[closed date] )
    )
)

average age = 
AVERAGEX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[open date] <= MAX ( 'Table'[closed date] )
            && 'Table'[closed date] >= MAX ( 'Table'[closed date] )
    ),
    MAX ( 'Table'[closed date] ) - 'Table'[open date] + 1
)

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@Anonymous , Refer if on the two blogs or the attached files after signature can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785