How to count until a certain value is reached

[Anonymous]

Hi! 

I am a beginner in Power BI Desktop and I kindly ask you to help me with a particular DAX formula.

I have a column with meetings ID and there can be only 2 meetings per quarter. How can I count them until 2 is reached? So, that means only two of the earliest meetings are counted and those which were arranged later are not counted. 

I hope I explained the task clearly. 

Thank you in advance. 

[amitchandak]

@Anonymous , if you have date we can have measure like with date Table

measure =
var _qtr =CALCULATE(Count(Table[meeting ID]),DATESQTD(('Date'[Date])))

return
sumx(values(Date[Qtr-year]),if(_qtr >=2,2,_qtr))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Sample data is something like this:

id of meeting	date
6247	2020-09-10 11:07:56.000000
5762	2021-07-07 10:30:00.000000
9856	2021-03-17 10:30:00.000000
4567	2021-04-28 10:30:00.000000

I also have other table connected where are boolean fields like IS CURRENT FQ, FIRST DAY OF FQ, LAST DAY OF FQ.

So, I need to count only the first 2 meetings of each quarter. When the count reaches 2, it stops counting. 

[Ashish_Mathur]

Hi,

Try this approach:

1. Using the Query Editor, remove the time stamps from the date column
2. Create a Calendar Table and write calculated column formulas to extract Year, Month Name, Month number and Quarter.  Using the "Sort By" feature, sort the Month name column by Month number
3. Create a relationship from the Date column of your dataset (from where you have removed the time stamp) to the Date column of your Date table
4. To your visual, drag the Year and Quarter from the Calendar Table
5. Write this measures

=IF(COUNTROWS(Data)>2,2,COUNTROWS(Data))

Hope this helps.

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can create three columns and a measure to meet your requirement.

1. Create year column, quarter column and year & quarter column.

Year = YEAR('Table'[Date])

Quarter = QUARTER('Table'[Date])

Year & Quarter = 'Table'[Year] &"-Q"& 'Table'[Quarter]

2. Create a measure and the result like this,

Measure = 
var _count = COUNT('Table'[id of meeting])
var _result = 
IF(
    _count>2,2,_count)
return
_result

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[Greg_Deckler]

@Anonymous Sample data would be great, but you could count rows that are earlier than the current row but greater than the start of the quarter like:

Column = 
  VAR __Table = FILTER('Table',[Date]<=EARLIER([Date] && [Quarter] = EARLIER([Quarter]) && [Year]=EARLIER([Year]))
RETURN
  COUNTROWS(__Table)