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

[Casey_Alderson]

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 🙂

Calculate Column = IF(TODAY() = 'Rolling Count of Unique Users'[Date], Count('Unique Learners'[Employee ID]), 0)

Any suggestions? Ideally simple solutions as I'm fairly green to Power BI and DAX.

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.

[Casey_Alderson]

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

[Ashish_Mathur]

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.

[Casey_Alderson]

Hi Ashish, I think it's still struggling to record the count each day and then use that data to create the trend.

Your function gives the correct count as of today and creates a trend line but it's a straight line giving every date the current count of employees. 

[Ashish_Mathur]

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

[Casey_Alderson]

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 · Accepted solution]

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]

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!!

[brandyn_sfetcu]

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

[Casey_Alderson]

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