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