Calculate max value from Mon-Sun for every week

user_19053026 0
Viewed 198

I have a table where I have to find the maximum weekly quantity for the data based on the date column in the past few years. In this example, say 06/07 is Monday and 06/12 is Sunday. Then in that week, the quantity 12 is the MAX value.

Quantity Date Time
1 2022-06-07 04:39:57.090
2 2022-06-07 04:39:58.850
3 2022-06-08 04:37:35.900
4 2022-06-08 04:37:37.247
5 2022-06-09 04:37:56.833
6 2022-06-09 04:37:58.190
7 2022-06-10 04:37:34.757
8 2022-06-10 04:37:36.103
9 2022-06-11 04:38:18.733
10 2022-06-11 04:38:20.100
11 2022-06-12 04:38:30.377
12 2022-06-12 04:38:31.833
13 2022-06-13 04:38:30.377
14 2022-06-14 04:38:31.833

I am trying to write a DAX script for this, but failing to do it because I am filtering on MAX date instead of value. Any help on how to modify this?

         CALCULATE(
            MAX( 'stock_xml'[Quantity] ),           
            FILTER( 'stock_xml',
                'stock_xml'[Date] = MAX( 'stock_xml'[Date] )
            )
        )
         RETURN 
            Stock
dax powerbi
1 Answers

Can you please try this below Measure script-

weekly_max = 
CALCULATE(
    MAX('your_table_name'[Quantity]),
    FILTER(
        ALL('your_table_name'),
        WEEKNUM('your_table_name'[Date],1) = WEEKNUM(MAX('your_table_name'[Date]),1)
    )
)

Sample output-

enter image description here

user_36523450
Related