Return Max Value based on dates

[RDzeketey]

Hi,

I have a date table and a data table. The date table has weekendig date. The Data table (Table 1) has Order Dates, Different Products and Order Qty. I want to calculate

1 - the max qty over a 52 week period and

2 - Identify the "Week Ending Date" when the Max value was reached.

Please see below

Thank you

Table 1
Order Date	Product	Order Qty
3/11/2020	A	1582
3/12/2020	B	3855
3/13/2020	C	3750
3/14/2020	A	4202
3/15/2020	B	3047
3/16/2020	C	4064
3/17/2020	A	3572
3/18/2020	B	4572
3/19/2020	C	3397
3/20/2020	A	4315
3/21/2020	B	3963
3/22/2020	C	3651
3/23/2020	A	5466
3/24/2020	B	4420
3/25/2020	C	3381
3/26/2020	A	3696
3/27/2020	B	3311
3/28/2020	C	3891
3/29/2020	A	3301
3/30/2020	B	3099
3/31/2020	C	3096
4/1/2020	A	3152

Date Table
Date Key	Week Ending
6/6/2019	6/8/2019
6/7/2019	6/8/2019
6/8/2019	6/8/2019
6/9/2019	6/15/2019
6/10/2019	6/15/2019
6/11/2019	6/15/2019
6/12/2019	6/15/2019
6/13/2019	6/15/2019
6/14/2019	6/15/2019
6/15/2019	6/15/2019
6/16/2019	6/22/2019
6/17/2019	6/22/2019
6/18/2019	6/22/2019
6/19/2019	6/22/2019
6/20/2019	6/22/2019
6/21/2019	6/22/2019
6/22/2019	6/22/2019
6/23/2019	6/29/2019
6/24/2019	6/29/2019
6/25/2019	6/29/2019
6/26/2019	6/29/2019
6/27/2019	6/29/2019

[v-lili6-msft]

hi @RDzeketey 

For your case, you need to do it as this:

Step1:

Create a relationship by order date and datekey

Step2:

You'd better create  a year column in date table

Step3:

Create two measure

max qty over a 52 week period = CALCULATE(MAX('Table 1'[Order Qty]),FILTER(ALLEXCEPT('Date Table','Date Table'[Year]),ISBLANK(SUM('Table 1'[Order Qty]))=FALSE()))

Week Ending Date of maxqty = IF(ISBLANK([max qty over a 52 week period])=FALSE(),IF([max qty over a 52 week period]=SUM('Table 1'[Order Qty]),MAX('Date Table'[Week Ending])))

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[RDzeketey]

Hi, @v-lili6-msft ,

Your solution gives the same value for the 52week max for all dates in the table and sometimes the Max value is lower than the week being compared to. Also, on the date of Max value, the date only shows up in the row that the max vale was in. Is it possible to have the date in each week ending date so each week, one can determine when the max value happened? PLease see below for my outcome.

Thank you