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    Updatio...
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

     

     

     

     

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.