topic Re: Calculate No. of Weeks & No. of Months based on MEASURE Condition in Desktop
https://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381730#M589126
<P>Hi <LI-USER uid="227497"></LI-USER> </P><P>The measures used are for</P><P>1. <SPAN>% Complete_Qty = </SPAN><SPAN>sum('Quantity'[Qty])/CALCULATE(sum('Quantity'[Qty]),all('_Calendar'))</SPAN></P><DIV><DIV><SPAN><SPAN>2. </SPAN></SPAN><SPAN>% Complete_Qty_Cumulative = </SPAN><SPAN>CALCULATE(<BR />[% Complete_Qty],<BR />FILTER(<BR />ALLSELECTED('_Calendar'[Date]),<BR />ISONORAFTER('_Calendar'[Date], MAX('_Calendar'[Date]), DESC)<BR />)<BR />))</SPAN></DIV><DIV> </DIV><DIV><SPAN>Based on the "<SPAN>% Complete_Qty_Cumulative", i need to count the number of weeks and number of months where "% Complete_Qty_Cumulative" is betweeen 10% and 90%</SPAN></SPAN></DIV><DIV><SPAN><SPAN>In the below same, the RESULT for Count of MONTHs should be <STRONG>7</STRONG></SPAN></SPAN></DIV><DIV> </DIV><DIV><TABLE><TBODY><TR><TD>MMM-YY</TD><TD>Qty Period</TD><TD>Qty Period Cumulative</TD><TD>Qty Progress %</TD><TD>Qty Progress % Cumulative</TD></TR><TR><TD>Jul-22</TD><TD>331,855</TD><TD>331,855</TD><TD>12.76%</TD><TD>12.76%</TD></TR><TR><TD>Aug-22</TD><TD>345,008</TD><TD>676,863</TD><TD>13.27%</TD><TD>26.03%</TD></TR><TR><TD>Sep-22</TD><TD>467,422</TD><TD>1,144,285</TD><TD>17.97%</TD><TD>44.00%</TD></TR><TR><TD>Oct-22</TD><TD>307,273</TD><TD>1,451,558</TD><TD>11.81%</TD><TD>55.81%</TD></TR><TR><TD>Nov-22</TD><TD>297,517</TD><TD>1,749,075</TD><TD>11.44%</TD><TD>67.25%</TD></TR><TR><TD>Dec-22</TD><TD>378,958</TD><TD>2,128,033</TD><TD>14.57%</TD><TD>81.82%</TD></TR><TR><TD>Jan-23</TD><TD>268,027</TD><TD>2,396,060</TD><TD>10.31%</TD><TD>92.13%</TD></TR><TR><TD>Feb-23</TD><TD>204,719</TD><TD>2,600,779</TD><TD>7.87%</TD><TD>100.00%</TD></TR></TBODY></TABLE></DIV></DIV>Sat, 19 Sep 2020 18:08:31 GMThackfifi2020-09-19T18:08:31ZCalculate No. of Weeks & No. of Months based on MEASURE Condition
https://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381069#M588918
<P>Good Day Everyone.</P><P>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)</P><P>Kindly note the Quantity % Progress Cumulative is a MEASURE. I have a calendar table which has the Date, Month & Week Ending columns.</P><P>Tried this calculation but it did not work for Counting No. of MONTHS --></P><P> </P><P>IF([% Complete_Qty]>=0.1 && [% Complete_Qty]<=0.9, DATEDIFF(FIRSTDATE(_Calendar[Date]),LASTDATE(_Calendar[Date]),MONTH),0)</P><P>It returns 0, works if i cross hightlight the months in the table.</P><P> </P><P>Cheers!!</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PowerBI Query 1.PNG" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/359119iE0C1DC479F3F4D07/image-size/large?v=1.0&px=999" role="button" title="PowerBI Query 1.PNG" alt="By Month" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">By Month</span></span></P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PowerBI Query 2.PNG" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/359118iF4386AB3E883E429/image-size/large?v=1.0&px=999" role="button" title="PowerBI Query 2.PNG" alt="By Week" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">By Week</span></span></P>Fri, 18 Sep 2020 20:29:42 GMThttps://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381069#M588918hackfifi2020-09-18T20:29:42ZRe: Calculate No. of Weeks & No. of Months based on MEASURE Condition
https://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381317#M588982
<LI-USER uid="27918" login="hackfifi"></LI-USER><BR />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??<BR /><BR />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? <BR /><BR />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. <BR /><BR />This will help others be able to respond too. <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span>Sat, 19 Sep 2020 04:02:37 GMThttps://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381317#M588982AllisonKennedy2020-09-19T04:02:37ZRe: Calculate No. of Weeks & No. of Months based on MEASURE Condition
https://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381730#M589126
<P>Hi <LI-USER uid="227497"></LI-USER> </P><P>The measures used are for</P><P>1. <SPAN>% Complete_Qty = </SPAN><SPAN>sum('Quantity'[Qty])/CALCULATE(sum('Quantity'[Qty]),all('_Calendar'))</SPAN></P><DIV><DIV><SPAN><SPAN>2. </SPAN></SPAN><SPAN>% Complete_Qty_Cumulative = </SPAN><SPAN>CALCULATE(<BR />[% Complete_Qty],<BR />FILTER(<BR />ALLSELECTED('_Calendar'[Date]),<BR />ISONORAFTER('_Calendar'[Date], MAX('_Calendar'[Date]), DESC)<BR />)<BR />))</SPAN></DIV><DIV> </DIV><DIV><SPAN>Based on the "<SPAN>% Complete_Qty_Cumulative", i need to count the number of weeks and number of months where "% Complete_Qty_Cumulative" is betweeen 10% and 90%</SPAN></SPAN></DIV><DIV><SPAN><SPAN>In the below same, the RESULT for Count of MONTHs should be <STRONG>7</STRONG></SPAN></SPAN></DIV><DIV> </DIV><DIV><TABLE><TBODY><TR><TD>MMM-YY</TD><TD>Qty Period</TD><TD>Qty Period Cumulative</TD><TD>Qty Progress %</TD><TD>Qty Progress % Cumulative</TD></TR><TR><TD>Jul-22</TD><TD>331,855</TD><TD>331,855</TD><TD>12.76%</TD><TD>12.76%</TD></TR><TR><TD>Aug-22</TD><TD>345,008</TD><TD>676,863</TD><TD>13.27%</TD><TD>26.03%</TD></TR><TR><TD>Sep-22</TD><TD>467,422</TD><TD>1,144,285</TD><TD>17.97%</TD><TD>44.00%</TD></TR><TR><TD>Oct-22</TD><TD>307,273</TD><TD>1,451,558</TD><TD>11.81%</TD><TD>55.81%</TD></TR><TR><TD>Nov-22</TD><TD>297,517</TD><TD>1,749,075</TD><TD>11.44%</TD><TD>67.25%</TD></TR><TR><TD>Dec-22</TD><TD>378,958</TD><TD>2,128,033</TD><TD>14.57%</TD><TD>81.82%</TD></TR><TR><TD>Jan-23</TD><TD>268,027</TD><TD>2,396,060</TD><TD>10.31%</TD><TD>92.13%</TD></TR><TR><TD>Feb-23</TD><TD>204,719</TD><TD>2,600,779</TD><TD>7.87%</TD><TD>100.00%</TD></TR></TBODY></TABLE></DIV></DIV>Sat, 19 Sep 2020 18:08:31 GMThttps://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381730#M589126hackfifi2020-09-19T18:08:31ZRe: Calculate No. of Weeks & No. of Months based on MEASURE Condition
https://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381834#M589152
If you want to count months, then use VALUES(_Calendar[Month]) as your table for the calculation: <BR /><BR />Number Months = COUNTROWS(FILTER(VALUES(_Calendar[Month])), [% Complete_Qty]>=0.1 && [% Complete_Qty]<=0.9))<BR /><BR />If you want to use that in the above matrix, you may need to add ALL() or ALLSELECTED filter in there as wellSat, 19 Sep 2020 21:57:55 GMThttps://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381834#M589152AllisonKennedy2020-09-19T21:57:55ZRe: Calculate No. of Weeks & No. of Months based on MEASURE Condition
https://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381842#M589155
<P><LI-USER uid="227497"></LI-USER> - Perfect! That worked...i just had to use "<SPAN>% Complete_Qty_Cumulative"! Cheers</SPAN></P>Sat, 19 Sep 2020 22:16:39 GMThttps://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381842#M589155hackfifi2020-09-19T22:16:39ZRe: Calculate No. of Weeks & No. of Months based on MEASURE Condition
https://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381847#M589158
Yes, of course, sorry for the typo but glad you figured it out. <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span>Sat, 19 Sep 2020 22:24:08 GMThttps://community.powerbi.com/t5/Desktop/Calculate-No-of-Weeks-amp-No-of-Months-based-on-MEASURE/m-p/1381847#M589158AllisonKennedy2020-09-19T22:24:08Z