cancel
Showing results for
Did you mean:
Frequent Visitor

## Average total calculated by weekly date

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
1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Frequent Visitor

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

Community Support

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.

Frequent Visitor

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

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.

Announcements

#### Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

#### Power BI May 2023 Update

Find out more about the May 2023 update.