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

[hackfifi]

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

[AllisonKennedy]

@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. 🙂

[hackfifi]

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

[AllisonKennedy · Accepted solution]

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

[hackfifi]

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

[AllisonKennedy]

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