Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello All,
I have a daily stock report that I combine in Power query, and the report after combining would look like this:
Date | Calendar Week | Contract | Provider | Available Stock |
1/2/2023 | 2 | 66666 | AOI | 30 |
1/2/2023 | 2 | 55555 | AOI | 20 |
1/2/2023 | 2 | 44444 | FBL | 60 |
1/3/2023 | 2 | 66666 | AOI | 40 |
1/3/2023 | 2 | 55555 | AOI | 15 |
1/3/2023 | 2 | 44444 | FBL | 20 |
So the first 3 entries are from the report of 02.01.2023 and the next are from the report of 03.01.2023 and so on..
I need a dax formula to calculate the maximum stock available in every calendar week to use in a pivot table, by summing up the available stock for all the contracts everyday in the week and gives value of the maximum day, and your help on this is much appreciated.
Thank you,
Altaiba
To calculate the maximum stock available in every calendar week, you can use the following DAX formula:
This formula first groups the data by the Calendar Week and Contract columns, and calculates the total stock available for each combination using the SUM function. It then creates a new column called "Total Stock" to store the calculated value.
The formula then uses the MAXX function to iterate over the grouped data and return the maximum value of the "Total Stock" column.
You can add this formula to your data model and use it in a pivot table to get the maximum stock available for each calendar week.