Forum Discussion
Count rows before a given date for each row
You could add a calculated column (Table name is "Registries")
Count = VAR UpdationDate = Registries[Updation_date] RETURN COUNTROWS( FILTER(Registries,Registries[Updation_date] <= UpdationDate))That should result in:
Hope this helps
JJ
You could add a calculated column (Table name is "Registries")
Count =
VAR UpdationDate = Registries[Updation_date]
RETURN
COUNTROWS(
FILTER(Registries,Registries[Updation_date] <= UpdationDate))That should result in:
Hope this helps
JJ
You could add a column, but I recommend you don't.
Learn about calculated columns vs measures in Power BI
Hi DoubleJ . Your solution worked like a charm. Now I'm facing another situation, and I'd like to know if it's possible to implement it. Let's suppose that some of the rows are duplicated in any of its values. For example, there are two different entries for the unique_identifier 123:
Unique_identifier Updation_date Source My_new_measure
123 01/01/1999 A 1
242 01/02/1999 B 2
264 01/03/1999 A 3
What I want is My_new_measure to count only the number of unique_identifiers before date. For example, for the first two rows there is only one unique_identifier, so My_new_measure should be 1 in both cases. And, for example, if I add a fifth row whose identifier already exists (242, for example), the measure shouln't increase, because the identifier in the row did already exist. A sixth row with a new unique identifier should result in an increase. I'll show you with an example:
Unique_identifier Updation_date Source My_new_measure
123 01/01/1999 A 1
123 01/02/1999 A 1 -->the count maintains because the identifier already exists (123)
242 01/02/1999 B 2 -->the count increases: new identifier
264 01/03/1999 A 3 -->the count increases:new identifier
242 12/12/1999 A 3 -->the count maintains because the identifier already exists (242)
325 12/01/2000 A 4 -->the count increases:new identifier
Thanks all for your help :)
