Calculate weekly average based on selected time periods

[ryan_b_fiting]

Hello Community - 

I am looking to get a weekly average for the most recent  3 week period based on whatever time period is selected.

I also need to be able to get that average by users that are sliced or filtered on.

Below is a small data set sample and there results I am expecting for the period of 1/1/21-1/22/21.

User ID	Sale Date	PO Count		Period Start	Period End
123	1/1/2021	100		1/1/2021	1/22/2021
123	1/8/2021	200
234	1/1/2021	40
234	1/8/2021	500		User	3 Wk Avg
234	1/15/2021	600		Overall	1,570.00
234	1/22/2021	300		123	66.67
345	1/22/2021	900		234	466.67
355	1/8/2021	750		345	300.00
355	1/15/2021	700		355	736.67
355	1/22/2021	760		399	-
399	1/1/2021	600

So for the example above, I would want the sliced date range (1/1/21-1/22/21) to remain the same for all users.  So if I select the use 399, I should get 0 as my average because there is no PO's in the 3 week average range (most recent 3 weeks would be 1/8/21-1/22/21).

Any help is appreciated with this.  I have been going in circles on it, and I have a feeling it is something relatively easy that I am just missing because I have been staring at it for so long.

Thanks Commuity!

Ryan

[Jihwan_Kim · Accepted solution]

Hi, @ryan_b_fiting 

Please check the below picture and the sample pbix file's link down below.

Avg 3weeks =
VAR latestweeknumber =
MAX ( 'Calendar'[Week of Year] )
VAR threeweeksperiod = latestweeknumber - 2
RETURN
DIVIDE (
CALCULATE (
SUM ( Data[PO Count] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Week of Year] >= threeweeksperiod
&& 'Calendar'[Week of Year] <= latestweeknumber
)
),
3
)

https://www.dropbox.com/s/9jm1dau86n2hdfc/fiting.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[samdthompson]

Hello, try this instead, you might need to change table names etc:

CALCULATE(

AVERAGEX(

    SUMMARIZE('Table','Table'[Date],"PO", CALCULATE(COUNT('Table'[PO ID])))

    ,[PO]),

   FILTER(ALL('Table'[Date]),

   'Table'[Date] <= MAX('Table'[Date]) &&

   'Table'[Date] > (MAX('Table'[Date])-21)

   )

)

[samdthompson]

Hello, try something like this (have assumed you already have a PO count measure):

=CALCULATE([PO_Count],DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-21,DAY))

[ryan_b_fiting]

@samdthompson thanks for the reply.  This does not appear to provide the correct solution. 

It seems that whenever I select a user the dates always get adjusted to the users date range for the average.

Example: I have all of 2021 selected (latest sale date is 1/22/21), when I select user 399, it is still giving me a 200 average when it should be 0 because user 399 has 0 PO's in the 3 Week Average range.