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 5...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity 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