Count rows before a given date for each row

[Anonymous]

Hi, 

I'm facing a problem with  a table in which I'd like to calculate the number of rows that exist until a given date. My table has the following structure: 

Unique_identifier    Updation_date     Source

123                          01/01/1999          A

242                          01/02/1999          B

264                          01/03/1999          A

And what I'd like to get is, for each row, the number of registries that are included until this row. For example, for second row, given that its Updation_date is 01/02/1999, I'd like to obtain two, because there are two rows that are included until that date: 01/01/1999 and the record date itself: 01/02/1999. I've tried with the following formula: 

My_measure = CALCULATE (COUNTROWS ( 'my_table' );

FILTER ( ALL ( 'my_table' );'my_table'[Updation_date] <= MAX('my_table'[Updation_date])))

And with this (deleting "MAX"):

My_measure = CALCULATE (COUNTROWS ( 'my_table' );

FILTER ( ALL ( 'my_table' );'my_table'[Updation_date] <= ('my_table'[Updation_date])))

But it's returning me always 3 (the total number of rows included until latest date) for all rows, instead of what I'd like to obtain, that is:

Unique_identifier    Updation_date     Source    My_measure

123                          01/01/1999          A            1

242                          01/02/1999          B            2

264                          01/03/1999          A            3

Could you please give me a hand? I think this is not such a difficult problem, but I can't get a solution :(.

[DoubleJ]

Hi

I don't want to seem pedantic but first let's clarify on 2 terms

- If you add a new column o a table it's just that: a calculated column and NOT a measure

- A unique identifier holds unique values by its very definition.

I am not aware of a way to do what you want to do. Power BI does not read the rows in the order as they are displayed in the Data pane (it is a column based system after all). Without a true unique identifier Power BI has no way to know which 242 is first and which is second. You would need a logic derived from the data that determines the order of the entries.

[DoubleJ · Accepted solution]

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

[Anonymous]

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 🙂

[MattAllington]

You could add a column, but I recommend you don't. 

Learn about calculated columns vs measures in Power BI

[MattAllington]

This is a very complex issue that I wont go into.  The solution to your problem is to 

1. add a calendar table 
2. link the tables
3. put the date from your calendar table in the visual
4. write the same (first measure) that refers to the date in the calnedar table  - it will work.

You can learn about calendar tables in Power BI at my blog