Forum Discussion
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 day as my understanding is this column will simply revert back to zero when yesterday is no longer today 🙂
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]) ))+0Result 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.
10 Replies
Hey Casey_Alderson
Have you tried something like this? Also, if this solution works for you. Feel free to leave a thumbs up.
Total_Count_Until_Today =
CALCULATE (
COUNT('Unique Learners'[Employee ID]),
FILTER (
'Rolling Count of Unique Users',
'Rolling Count of Unique Users'[Date] <= TODAY()
)
)Hy Brandyn,
This returned the same figure in every row of the new column. I believe that would have worked if I already had each daily count in the referenced column and you were attempting to get the running total. But I don't have the daily count rather I'm trying to devise a function or set of functions that will automate the generation of that count for me.
Thanks!
Casey
Hi,
Share some data to work with, explain the question and show the expected result.
Hi Ashish,
My primary table is a list of employees that have taken a training program. It contains standard data you would assume as far as who took what course and when. Then in another table I remove duplicates based on the employee ID so that I have a running list of Unique Employee Learners that grows over time and as new employees take training.
So today it may be:
Employee - 1
Employee - 2
Employee - 3
then tommorow
Employee - 1
Employee - 2
Employee - 3
Employee - 4
Employee - 5
I want to capture the count of the unique employee IDs over time (create a column that essentially is the trend of unique users). So I created another table that contains successive dates (capturing the count monthly would be fine but daily seemed easier) and then a column with the function:
Calculate Column = IF(TODAY() = 'Rolling Count of Unique Users'[Date], Count('Unique Learners'[Employee ID]), 0)This is sucessfully given me my desired measure each day but as the days change the calculation just moves down the list and doesn't keep the record of what the count was in prior days. There may be far easier or more effective solutions to the overall problem, this is just the only idea I could come up with.Thanks again,
Casey
Hi,
Create a Calendar Table and build a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table. In the Calendar Table, write calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number. To your visual, drag Year and Month name from the Calendar Table. Write this measure
Measure = calculate(distinctcount(Data[Employee ID]),datesbetween(calendar[Date],minx(all(calendar),calendar[Date]),max(calendar[date])))
Hope this helps.
