Forum Discussion
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...
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
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