Forum Discussion
Helper IIIPOWER BI DAX MEASURE
Hi Julier ,
I have reproduce your scenario in Power BI Desktop and validate the requirement.To achieve the expected result, I implemented the following approach:
- Created a proper Date table and used it for time intelligence
- Built a complete Site × Date dataset to ensure all dates are present
- Used SUM with DATESMTD for cumulative calculations
- Calculated Remaining Capacity as:
Monthly Capacity – Used Capacity MTD
With this setup:
- Used Capacity increases day by day
- Remaining Capacity decreases across the month
- Demand can be compared correctly against capacity
I’m getting the expected output based on your requirement.
For your reference, I’ve attached the sample .pbix file with the complete working solution.
Thank You.
Hi,
1.if you sum your daily capacity across the month, you will get the monthly capacity. If you use max here, perhaps you want to avoid duplicate of daily capacity since each site can have many capacities in a day? First is to get your daily capacity, then sum up across the month using sumx of your daily capacity from beginning of month to end of month
2.used capacity mtd = sum daily capacity used until current date, also using sumx, something like this:
calculate(
sumx(values(Date[Date]), [daily capacity used]),
3. remaining monthly capacity = monthly capacity point 1 above - used capacity mtd point 2 above.