Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

Need Help
  • DoubleJ's avatar
    DoubleJ
    7 years ago

    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

     

     

     

     

5 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    7 years ago

    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

  • DoubleJ's avatar
    DoubleJ
    Solution Supplier
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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 :)

  • DoubleJ's avatar
    DoubleJ
    Solution Supplier
    7 years ago

    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.