How to count until a certain value is reached

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.

Need Help

Show and Tell

Tutorial Requests

v-zhenbw-msft
5 years ago
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.
How to count until a certain value is reached.pbix19 KB

5 Replies

Greg_Deckler
Community Champion
5 years ago
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)
amitchandak
Super User
5 years ago
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
  Not applicable
  5 years ago
  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.
  - v-zhenbw-msft
    Community Support
    5 years ago
    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.
    How to count until a certain value is reached.pbix19 KB