Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |