Forum Discussion
Rolling Counts
Hi.
I am struggling to know which is the best way to add a column to my data which will produce a count of how many times a value appears; I don't want a simple count - what I am after is a formula which will increment by 1 each time another instance of the value appears in the given column.
The images below are simply a sample - if I can undrstand the principle on some basic data, my theory is that I can then develop my learning and make more contextual using filters as I go on, but for now I just need to know the basic technique and formula.
OK, the first image is my data in Excel, but with a column showing the count of names in column A, i.e. each time a new instance of the name is encountered in column 'Name', the count goes up by 1.
The other images show my data source file (but with the formula 'in progress', so that you can see what it does.
The third image is how my data file looks in Power BI, i.e. with no 'Count of Name' column.
Ultimately I want to have a calculated column that shows the count of each name, so that I can then use another formula to extract the 5 most recent instances, i.e. the 5 most recent rows for each name.
Count of Name CC : =
VAR currentname = Data[Name]
VAR currentdate = Data[Date]
RETURN
COUNTROWS (
FILTER ( Data, Data[Name] = currentname && Data[Date] <= currentdate )
)
3 Replies
Count of Name CC : =
VAR currentname = Data[Name]
VAR currentdate = Data[Date]
RETURN
COUNTROWS (
FILTER ( Data, Data[Name] = currentname && Data[Date] <= currentdate )
)Buckswriter
Add this new column to your table:Count = COUNTROWS( SELECTCOLUMNS( FILTER( Table1 , Table1[Date] <= EARLIER(Table1[Date]) && Table1[Name] = EARLIER(Table1[Name])) , "Name" , Table1[Name] ) )Hi Buckswriter ,
Can you paste this data here instead of sharing the image so that we can check this ?
