Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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's avatar
    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.

5 Replies

  • Greg_Deckler's avatar
    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's avatar
    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Sample data is something like this:

      id of meetingdate
      62472020-09-10 11:07:56.000000
      57622021-07-07 10:30:00.000000
      98562021-03-17 10:30:00.000000
      45672021-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's avatar
        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.