cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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
Super User II
Super User II

@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

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%

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

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

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!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors