Forum Discussion

Casey_Alderson's avatar
Casey_Alderson
Helper I
2 years ago
Solved

Today Function referencing a Date Column to Count Rows as of Today then Retain Count

Hi All,    I'm trying to create a trend column by counting rows in another table daily.   I used the below code to create the initial function but I'm not sure how then to retain my counts each d...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Casey_Alderson ,

     

    Here I create a sample to have a test.

    Unique Learners:

    I suggest you to create a 'Rolling Count of Unique Users' as a calculated table.

    Rolling Count of Unique Users = CALENDAR(MIN('Unique Learners'[Date]),TODAY())

    Calculated Column:

    Total_Count_Until_Today = 
CALCULATE (
COUNT('Unique Learners'[Employee ID]),
FILTER (
'Unique Learners',
'Unique Learners'[Date]<= EARLIER('Rolling Count of Unique Users'[Date])
))+0

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

I cannot help you with getting your file and seeing the exact expected result.

Casey_Alderson's avatar
Casey_Alderson
Helper I
2 years ago

I appreciate your time trying to help. I've been researching the community quite a bit and it seems this functionality doesn't exsit within a BI only solution. I might be able to incorporate Power Automate and get something to work. 

 

Thanks again and have a great week!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Casey_Alderson ,

     

    Here I create a sample to have a test.

    Unique Learners:

    I suggest you to create a 'Rolling Count of Unique Users' as a calculated table.

    Rolling Count of Unique Users = CALENDAR(MIN('Unique Learners'[Date]),TODAY())

    Calculated Column:

    Total_Count_Until_Today = 
CALCULATE (
COUNT('Unique Learners'[Employee ID]),
FILTER (
'Unique Learners',
'Unique Learners'[Date]<= EARLIER('Rolling Count of Unique Users'[Date])
))+0

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Casey_Alderson's avatar
      Casey_Alderson
      Helper I
      2 years ago

      This worked amazingly! I did have to create the Date for the Unique Learners table but that was as easy as a calculated column to filter that table by the Employee ID and pull the earliest date they show up on the report. From there is was really straight forward to use your calculations. Thank you so much!!