Forum Discussion

Tom_pbi's avatar
Tom_pbi
Regular Visitor
4 years ago
Solved

Count Help

Hi all,

 

I am having an issue getting the correct formula using COUNT. My scenario is that I have a column of dates and I want another column that counts the occurence of the same date cummulatively but distinctly for different dates. Example below:

I think the measure I have written so far would just confuse things but I am using COUNT and FILTER. Any help would be much appreciated.

 

Thanks,

Tom

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Tom_pbi ,

     

    Please add the Index column in Power Query Editor.

     

     

    Then create the column or measure.

     

    Column = COUNTROWS(FILTER('Table','Table'[Date] = EARLIER('Table'[Date]) && 'Table'[Index] <= EARLIER('Table'[Index])))
    Measure = COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Date] = MAX('Table'[Date]) && 'Table'[Index] <= MAX('Table'[Index])))

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • Syk's avatar
    Syk
    Resident Rockstar
    4 years ago

    You should be able to get this by summing your occurence field (value in screenshot). The first table is the sample you provided and the table on the right is just telling the value field to display as a sum. 

     

    You can also do this with count but it will basically just count the # of times a date appears with a value.

     

    • Tom_pbi's avatar
      Tom_pbi
      Regular Visitor
      4 years ago

      Thanks but I think maybe I wasn't clear. The value field in your above screenshot is what I want to get, I don't need a sum of those values. 

      • Syk's avatar
        Syk
        Resident Rockstar
        4 years ago

        Ah! You provided an example of what you're trying to achieve.. Can you send an example of your current data? You mention there are a column of dates, you're just trying to count them in succession?

  • Tom_pbi's avatar
    Tom_pbi
    Regular Visitor
    4 years ago

    Bumping this if anyone would be able to give some advice. Thanks!

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi Tom_pbi ,

     

    Please add the Index column in Power Query Editor.

     

     

    Then create the column or measure.

     

    Column = COUNTROWS(FILTER('Table','Table'[Date] = EARLIER('Table'[Date]) && 'Table'[Index] <= EARLIER('Table'[Index])))
    Measure = COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Date] = MAX('Table'[Date]) && 'Table'[Index] <= MAX('Table'[Index])))

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.