cancel
Showing results for
Did you mean:
Highlighted
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 Month

By Week

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
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.

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

excelwithallison.com

5 REPLIES 5
Highlighted
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.

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

excelwithallison.com

Highlighted
Helper IV

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-YY Qty Period Qty Period Cumulative Qty Progress % Qty Progress % Cumulative Jul-22 331,855 331,855 12.76% 12.76% Aug-22 345,008 676,863 13.27% 26.03% Sep-22 467,422 1,144,285 17.97% 44.00% Oct-22 307,273 1,451,558 11.81% 55.81% Nov-22 297,517 1,749,075 11.44% 67.25% Dec-22 378,958 2,128,033 14.57% 81.82% Jan-23 268,027 2,396,060 10.31% 92.13% Feb-23 204,719 2,600,779 7.87% 100.00%
Highlighted
Super User II
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.

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

excelwithallison.com

Highlighted
Helper IV

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

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

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

excelwithallison.com

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

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