Average total calculated by weekly date

[KatBous]

Hi to all and many thanks in advance!

I have this information. 

I produce a weekly report. I want to calculate by the date of each report a) how many positions were still open by each report date, b) how many positions had closed by by each report date, c) what the average time to recruit (for all positions) was by each report date.

I do not know if it plays a role, but the report is every Thursday.

Position Title	Date position opened	Date position closed	Time to recruit
a	01/01/2022	20/01/2022	19
b	15/01/2022	30/01/2022	15
c	16/01/2022	18/01/2022	2
d	14/03/2022	16/03/2022	2
e	20/03/2022	05/05/2022	45
f	25/03/2022	25/4/2022	30
g	25/4/2022	01/5/2022	7

[KatBous]

Yes, it works great! Thank you @v-zhangti !

[v-zhangti]

Hi, @KatBous 

You can try the following methods.

Create a new date table and calculate Thursday.

Date = CALENDAR(MIN('Table'[Date position opened]),MAX('Table'[Date position closed]))

Column:

Weekday = WEEKDAY([Date],2)

Extract the date of each report.

New table:

Report date = FILTER('Date',[Weekday]=4)

Measure:

Count open =
CALCULATE (
    COUNT ( 'Table'[Position Title] ),
    FILTER (
        ALL ( 'Table' ),
        [Date position opened] <= SELECTEDVALUE ( 'Report date'[Date] )
            && [Date position closed] >= SELECTEDVALUE ( 'Report date'[Date] )
    )
)

Count closed =
CALCULATE (
    COUNT ( 'Table'[Position Title] ),
    FILTER (
        ALL ( 'Table' ),
        [Date position closed] <= SELECTEDVALUE ( 'Report date'[Date] )
    )
)

Average =
CALCULATE (
    AVERAGE ( 'Table'[Time to recruit] ),
    FILTER (
        ALL ( 'Table' ),
        [Date position closed] <= SELECTEDVALUE ( 'Report date'[Date] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[KatBous]

Thank you so much @v-zhangti  for the immediate and detailed reply!!!

One info that I forgot to include in my example table (new one below) is that some rows have blank "Date position closed" and "Time to recruit", as some positions are still open when I draft the report. How should I change the measures?

And also, using the measures, the table in PowerBi stops one week before the last week. It stops in 10 November while the last report date is 17 November.

Position Title	Date position opened	Date position closed	Time to recruit
a	18/10/2022	19/10/2022	2,00
b	18/10/2022	01/11/2022	15,00
c	20/10/2022
d	25/10/2022
e	25/10/2022
f	25/10/2022
g	25/10/2022
h	04/11/2022	14/11/2022	11,00
i	08/11/2022

[v-zhangti · Accepted solution]

Hi, @KatBous 

You can try the following methods.
Measure:

Count open =
VAR _N1 =
    CALCULATE (
        COUNT ( 'Table'[Position Title] ),
        FILTER (
            ALL ( 'Table' ),
            [Date position opened] <= SELECTEDVALUE ( 'Report date'[Date] )
                && [Date position closed] >= SELECTEDVALUE ( 'Report date'[Date] )
                && [Date position closed] <> BLANK ()
        )
    )
VAR _N2 =
    CALCULATE (
        COUNT ( 'Table'[Position Title] ),
        FILTER (
            ALL ( 'Table' ),
            [Date position closed] = BLANK ()
                && [Date position opened] <= SELECTEDVALUE ( 'Report date'[Date] )
        )
    )
RETURN
    _N1 + _N2

Count closed = 
CALCULATE (
    COUNT ( 'Table'[Position Title] ),
    FILTER (
        ALL ( 'Table' ),
        [Date position closed] <= SELECTEDVALUE ( 'Report date'[Date] )
            && [Date position closed] <> BLANK ()
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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