cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Calculate No. of Weeks & No. of Months based on MEASURE Condition

Good Day Everyone.

I need a measure calculation to count the number of MONTHs (measure 1) & number of WEEKS (measure 2) in the condition range of 10% to 90% for (Quantity % Progress Cumulative)

Kindly note the Quantity % Progress Cumulative is a MEASURE. I have a calendar table which has the Date, Month & Week Ending columns.

Tried this calculation but it did not work for Counting No. of MONTHS -->

 

IF([% Complete_Qty]>=0.1 && [% Complete_Qty]<=0.9, DATEDIFF(FIRSTDATE(_Calendar[Date]),LASTDATE(_Calendar[Date]),MONTH),0)

It returns 0, works if i cross hightlight the months in the table.

 

Cheers!!

 

By MonthBy Month

By WeekBy Week

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Calculate No. of Weeks & No. of Months based on MEASURE Condition

If you want to count months, then use VALUES(_Calendar[Month]) as your table for the calculation:

Number Months = COUNTROWS(FILTER(VALUES(_Calendar[Month])), [% Complete_Qty]>=0.1 && [% Complete_Qty]<=0.9))

If you want to use that in the above matrix, you may need to add ALL() or ALLSELECTED filter in there as well

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

Re: Calculate No. of Weeks & No. of Months based on MEASURE Condition

@hackfifi
Can you please share the sample raw data too and the value of the measure working and not working? Where are you trying to put this measure - I don't see it in the screenshots??

I suspect this is related to not having the needed row context to perform the calculation. What is the DAX for Quantity % Progress Cumulative ? What is the DAX for % Complete_Qty? What is the name of the measure you provided the DAX for?

Sorry for all the questions, but since your data is new to me, I just need lots of info to understand what you're trying to do.

This will help others be able to respond too. 🙂

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
Helper IV
Helper IV

Re: Calculate No. of Weeks & No. of Months based on MEASURE Condition

Hi @AllisonKennedy 

The measures used are for

1. % Complete_Qty = sum('Quantity'[Qty])/CALCULATE(sum('Quantity'[Qty]),all('_Calendar'))

2. % Complete_Qty_Cumulative = CALCULATE(
[% Complete_Qty],
FILTER(
ALLSELECTED('_Calendar'[Date]),
ISONORAFTER('_Calendar'[Date], MAX('_Calendar'[Date]), DESC)
)
))
 
Based on the "% Complete_Qty_Cumulative", i need to count the number of weeks and number of months where "% Complete_Qty_Cumulative" is betweeen 10% and 90%
In the below same, the RESULT for Count of MONTHs should be 7
 
MMM-YYQty PeriodQty Period CumulativeQty Progress %Qty Progress % Cumulative
Jul-22331,855331,85512.76%12.76%
Aug-22345,008676,86313.27%26.03%
Sep-22467,4221,144,28517.97%44.00%
Oct-22307,2731,451,55811.81%55.81%
Nov-22297,5171,749,07511.44%67.25%
Dec-22378,9582,128,03314.57%81.82%
Jan-23268,0272,396,06010.31%92.13%
Feb-23204,7192,600,7797.87%100.00%
Highlighted
Super User II
Super User II

Re: Calculate No. of Weeks & No. of Months based on MEASURE Condition

If you want to count months, then use VALUES(_Calendar[Month]) as your table for the calculation:

Number Months = COUNTROWS(FILTER(VALUES(_Calendar[Month])), [% Complete_Qty]>=0.1 && [% Complete_Qty]<=0.9))

If you want to use that in the above matrix, you may need to add ALL() or ALLSELECTED filter in there as well

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted
Helper IV
Helper IV

Re: Calculate No. of Weeks & No. of Months based on MEASURE Condition

@AllisonKennedy - Perfect! That worked...i just had to use "% Complete_Qty_Cumulative"! Cheers

Highlighted
Super User II
Super User II

Re: Calculate No. of Weeks & No. of Months based on MEASURE Condition

Yes, of course, sorry for the typo but glad you figured it out. 🙂

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors