Do you have to use blending or could you use a join?
No, I'm free to use anything: blending, joining, scaffolding.....
Fingered crossed there is a way to do this.
Please join on the employee ID and Employee Number instead of the date
I would not blend the data but us a date scaffold and Cartesian join the data to it - then use the scaffold dates in your calculation
the process is not difficulat - just a number of steps
I keep a scaffold solution on my Tableau public site at Tableau Public
it includes how to instructions - the example is counting active accounts - not quite yours but close enough
feel free to download the book - that's why it is there - and use as a model
it returns this
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
In the end I have to settle for a R-Python script to create the data_source on the way I wanted to be.
For sure this is not a long time solution as I have to run the script on weekly basis to create the data set. But it is an intermediate solution.
I'll close this (if I can).