Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Count for dates in date column

I thought this would be easy but I cannot for the love of anything make this work!

 

I have a date column (some blank rows, some rows with the same date sometimes). I want to create a calculated column that will count starting from the earliest date all the way to the most recent. For instances where there are multiple rows with the same date, it should have the highest count for those identical dates (so if there are two dates like 12/20/24 and 12/20/24 then both should be 2 rather than 1 then 2).  I want to use this in a line graph so that's why it's important to have that total for identicals so that line is straight between those dates showing that there hasn't been any changes.

 

Here's an example of how it should look like (did this in Excel)...

 

 

I can't wrap my head around how to do this, please help!

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    This calculated column formula works

    Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Dates]<=EARLIER(Data[Dates])))

    Hope this helps.

     

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    This calculated column formula works

    Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Dates]<=EARLIER(Data[Dates])))

    Hope this helps.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      That worked, thank you! However, I ran into a new problem while trying to use it in a line graph. If I have 3 and 3 for the same date, then on the line graph it adds it up to 6 rather than keep it 3. I showed a screenshot below. Any ideas on how to solve that? 

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        You are welcome.  Write this measure and drag it to the line visual

        Measure = min(Data[Column])

        Hope this helps.