Forum Discussion

RDzeketey's avatar
RDzeketey
Frequent Visitor
6 years ago

Return Max Value based on dates

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 DateProductOrder Qty
3/11/2020A1582
3/12/2020B3855
3/13/2020C3750
3/14/2020A4202
3/15/2020B3047
3/16/2020C4064
3/17/2020A3572
3/18/2020B4572
3/19/2020C3397
3/20/2020A4315
3/21/2020B3963
3/22/2020C3651
3/23/2020A5466
3/24/2020B4420
3/25/2020C3381
3/26/2020A3696
3/27/2020B3311
3/28/2020C3891
3/29/2020A3301
3/30/2020B3099
3/31/2020C3096
4/1/2020A3152

 

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

 

Need Help

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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's avatar
      RDzeketey
      Frequent Visitor
      6 years ago

      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