Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Try your skills in the Power BI Dataviz World Championship! Round one ends June 26. Join now

Reply
Julier
Helper III
Helper III

POWER BI DAX MEASURE

‎04-24-2026 09:31 PM

hello, i have a table which gives me capacity sqm that is using a calculated column to give me the daily capacity, i want to be able to show on a chart the monthly capacity and then the remaining monthly cpaacity as the days go by, i have 3 measure but it gives me the same value for monthly and remaining, can yu please let me know what I am doing wrong

Julier_0-1777091274656.pngJulier_1-1777091319872.pngJulier_2-1777091345448.png

Julier_3-1777091492919.png

 

SITE_IDDateCapacity SQM   
AAA27/04/2026133268.2   
AAA28/04/2026133268.2   
AAA29/04/2026133268.2   
AAA30/04/2026133268.2   
AAA01/05/2026122520.8   
AAA02/05/2026122520.8   
AAA03/05/2026122520.8   
AAA04/05/2026122520.8   
BBB27/04/202659578.2   
BBB28/04/202659578.2   
BBB29/04/202659578.2   
BBB30/04/202659578.2   
BBB27/04/202659578.2   
BBB28/04/202659578.2   
BBB29/04/202659578.2   
BBB30/04/202659578.2   
Message 1 of 18
1,476 Views
1 ACCEPTED SOLUTION
v-anbandari
Community Support
Community Support
In response to Julier

‎05-05-2026 03:28 AM

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.

View solution in original post

5177853.pbix
Message 14 of 18
931 Views
17 REPLIES 17
RDewi
Frequent Visitor

‎04-30-2026 06:59 AM

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]),

datesmtd(Date[Date])
)

3. remaining monthly capacity = monthly capacity point 1 above - used capacity mtd point 2 above.

Message 18 of 18
1,130 Views
0
Julier
Helper III
Helper III

‎04-30-2026 04:20 AM

Unfortuntely due to data confidentitality I am unable to share the file with you, is htere any other way we can resolve it?

Message 8 of 18
1,219 Views
0
FBergamaschi
Super User
Super User
In response to Julier

‎04-30-2026 05:39 AM

Yes, you can create a sample dataset with fake data

 

Best

 

FB

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Message 17 of 18
1,197 Views
0
v-anbandari
Community Support
Community Support
In response to Julier

‎04-30-2026 05:06 AM

Hi  @Julier  ,

You can share  sample data that represents the issue. Please do not include sensitive information or anything unrelated to the question. Also include the expected outcome based on the sample data.

For guidance:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

 

Message 9 of 18
1,203 Views
0
Julier
Helper III
Helper III
In response to v-anbandari

‎04-30-2026 05:55 AM

hI @FBergamaschi attached is the capacity data, one column shows daily capacity the Capacity SQM is 

Asset Group24/5 WorkingDaily_Capacity24/7 WorkingSITE_IDDateWeek CommencingCapacity SQM
Corrugators15639933nullAA04/05/202604/05/2026181933.3
Corrugators16203926nullAA22/06/202622/06/2026206797.5
Corrugators16203926nullAA23/06/202622/06/2026206797.5
Corrugators16203926nullAA30/06/202629/06/2026206797.5
HQQ1326748nullAA04/05/202604/05/202610540.26
HQQ1326748nullAA05/05/202604/05/202610540.26
HQQ1326748nullAA06/05/202604/05/202610540.26
FFFF1532418nullAA29/06/202629/06/202617747.27
FFFF1532418nullAA30/06/202629/06/202617747.27
FFFF1580048nullAA04/05/202604/05/202618711.23
FFFF1580048nullAA05/05/202604/05/202618711.23
Corrugatorsnull180396051BBB28/06/202622/06/2026601320.2
Corrugatorsnull180396051BBB29/06/202629/06/2026601320.2
SSSSnull18350091BBB30/06/202629/06/202661166.97
SSSSnull18350091BBB01/06/202601/06/202661166.97
SSSSnull18350091BBB02/06/202601/06/202661166.97
MMMnull8609081BBB22/05/202618/05/202627771.23
MMMnull8609081BBB23/05/202618/05/202627771.23
MMMnull8609081BBB24/05/202618/05/202627771.23
Corrugators18892900nullCCC15/06/202615/06/2026296430
Corrugators18892900nullCCC16/06/202615/06/2026296430
Corrugators18892900nullCCC17/06/202615/06/2026296430
SSSS11230215nullCCC27/06/202622/06/202641007.17
SSSS11230215nullCCC28/06/202622/06/202641007.17
FFFF1459299nullCCC10/06/202608/06/202615309.97
FFFF1459299nullCCC11/06/202608/06/202615309.97
FFFF1459299nullCCC12/06/202608/06/202615309.97

I also have a table with the demand as i wantto show the total monthly cpapcaty and how the demand sits below capacity and reducesas the days go on in the month, so I vassically want to see if an asset is overload as the month goes on.

Message 10 of 18
1,183 Views
0
Julier
Helper III
Helper III
In response to Julier

‎04-30-2026 05:56 AM

@FBergamaschi this is the table that has my capacity in it, I hopethis makes sense?

Asset Group24/5 WorkingDaily_Capacity24/7 WorkingSITE_IDDateWeek CommencingCapacity SQM
Corrugators15639933nullAA04/05/202604/05/2026181933.3
Corrugators16203926nullAA22/06/202622/06/2026206797.5
Corrugators16203926nullAA23/06/202622/06/2026206797.5
Corrugators16203926nullAA30/06/202629/06/2026206797.5
HQQ1326748nullAA04/05/202604/05/202610540.26
HQQ1326748nullAA05/05/202604/05/202610540.26
HQQ1326748nullAA06/05/202604/05/202610540.26
FFFF1532418nullAA29/06/202629/06/202617747.27
FFFF1532418nullAA30/06/202629/06/202617747.27
FFFF1580048nullAA04/05/202604/05/202618711.23
FFFF1580048nullAA05/05/202604/05/202618711.23
Corrugatorsnull180396051BBB28/06/202622/06/2026601320.2
Corrugatorsnull180396051BBB29/06/202629/06/2026601320.2
SSSSnull18350091BBB30/06/202629/06/202661166.97
SSSSnull18350091BBB01/06/202601/06/202661166.97
SSSSnull18350091BBB02/06/202601/06/202661166.97
MMMnull8609081BBB22/05/202618/05/202627771.23
MMMnull8609081BBB23/05/202618/05/202627771.23
MMMnull8609081BBB24/05/202618/05/202627771.23
Corrugators18892900nullCCC15/06/202615/06/2026296430
Corrugators18892900nullCCC16/06/202615/06/2026296430
Corrugators18892900nullCCC17/06/202615/06/2026296430
SSSS11230215nullCCC27/06/202622/06/202641007.17
SSSS11230215nullCCC28/06/202622/06/202641007.17
FFFF1459299nullCCC10/06/202608/06/202615309.97
FFFF1459299nullCCC11/06/202608/06/202615309.97
FFFF1459299nullCCC12/06/202608/06/202615309.97
Message 11 of 18
1,183 Views
0
FBergamaschi
Super User
Super User
In response to Julier

‎05-05-2026 04:24 AM

Sorry for late reply

 

Shall try to fix it tonight

 

best

 

 

Message 16 of 18
891 Views
0
v-anbandari
Community Support
Community Support
In response to Julier

‎05-04-2026 04:58 AM

Hi @Julier ,

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions. Your update will be valuable to the community and may assist others with similar concerns.


Thank you.

Message 12 of 18
1,003 Views
0
Julier
Helper III
Helper III
In response to v-anbandari

‎05-04-2026 11:26 PM

Hello, no this has not yet bee nresolved, i have uploaded some fake data but not yet resolved.

Message 13 of 18
978 Views
0
v-anbandari
Community Support
Community Support
In response to Julier

‎05-11-2026 03:17 AM

Hi @Julier ,

just following up to check whether the solution provided has helped to resolve your issue. If you are still facing any problems,  let us know .

Thank you.



Thank you.

Message 15 of 18
724 Views
0
v-anbandari
Community Support
Community Support
In response to Julier

‎05-05-2026 03:28 AM

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.

5177853.pbix
Message 14 of 18
932 Views
v-anbandari
Community Support
Community Support

‎04-30-2026 03:57 AM

Hi @Julier ,

Thank you to @techies , @FBergamaschi for the valuable insights shared.

Just following up to help investigate further, could you please share a sample dataset along with the PBIX file.

This will allow us to better understand the issue and provide a more accurate solution.

 

Thank you.

Message 7 of 18
1,228 Views
0
Julier
Helper III
Helper III

‎04-26-2026 07:25 AM

@FBergamaschi the daily capacity is the same capacity for each day so not additive once the day has elapsed i want to be able to deduct the days demand  from the capacity i have a measure for total demand 

Julier_0-1777211031610.png

a measure for monthly demand 

Julier_1-1777211063552.png

and a measure for remaining 

Julier_2-1777213272359.png

howver on one of the site its doesthis which is not correct

Julier_3-1777213322850.png

with these relationships?

Julier_4-1777213477803.png

 

Message 4 of 18
1,358 Views
0
FBergamaschi
Super User
Super User
In response to Julier

‎04-26-2026 11:19 PM

Hi @Julier 

I have to review the thing now as the code you show is now a bit different than before, maybe you are making different attempts of course.

 

To solve quickly, can you please share your pbix first so I can check the model and understand why you get always the same result? Then we come back to definitions and I shall solve it in a moment

 

Thanks

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Message 5 of 18
1,320 Views
0
Julier
Helper III
Helper III
In response to FBergamaschi

‎04-30-2026 04:15 AM

@FBergamaschi Unfortuntely due to data confidentitality I am unable to share the file with you, is htere any other way we can resolve it?

Message 6 of 18
1,221 Views
0
FBergamaschi
Super User
Super User

‎04-25-2026 04:28 AM

Hi @Julier,

1 - the daily capacity column shows the capcity of each day (additive capacity therefore)? In other words, the monthly capacity is the sum of the daily capacity for the days in each month? I see you are using MAX which I am bit confused about

2 - please show the code of all the measures, including nested measures

3 - please show the columns groupd in the visual and the data model

 

Thanks

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

 

 

Message 3 of 18
1,409 Views
0
techies
Super User
Super User

‎04-25-2026 03:22 AM

Hi @Julier Could you please share the Days Elapsed in Month measure? Also, in the Used Capacity measure, please replace SELECTEDVALUE with MAX. 

 

 

Power BI & Microsoft Fabric
PL-300 | DP-600 | DP-700 Certified
Message 2 of 18
1,423 Views
0

Helpful resources

Announcements
Fabric Data Days is here Carousel

Fabric Data Days 2026

Don't miss out on Data Days, June 15 through August 7. Learn Fabric, Power BI, SQL, AI and more.

May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All