cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Count rows before a given date for each row

 

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 :(.

1 ACCEPTED SOLUTION
Responsive Resident
Responsive Resident

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:
Count.png

Hope this helps

JJ

 

 

 

 

View solution in original post

5 REPLIES 5
Responsive Resident
Responsive Resident

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.

 

 

Responsive Resident
Responsive Resident

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:
Count.png

Hope this helps

JJ

 

 

 

 

View solution in original post

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 🙂

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

Learn about calculated columns vs measures in Power BI



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors