I didn't understand your problem. Please clarify.
Hi Hari Ankem
In August, the numerator should be
SUM( IF [Created On] <= [Date Calc]
AND [Dev Status ID] >= [Dev Status ID Parameter]
THEN 1 ELSE 0 END)
The Sum of [Dev Status ID] that has attained a [Dev Status ID Parameter] [Ex : 1 ]or more than that by the [Date Parameter] set
In the month of September, all the employees that has reached the [Dev Status ID Parameter] should be included in the calculation. In this scenario that would be all of the following data points, not just the emp "AS" on 24-Sep
If I use RUNNING_SUM, the numerator gets to 8 in the September as it's counting two records for emp "AS" when in fact it should be looking at the most recent [Dev Status ID] achieved as of [Date Parameter] and not both
Is this the expected output?
If yes, then note that I have mainly joined your data file with a list of dates and modified your formulas to get the desired output. Refer the attached updated workbook.
Book4.twbx 87.3 KB
Thank you very much for putting this together
Is there anyway that this solution can be achieved without joining to Dates dataset, as the original dataset might contain more than 4/5 years of data
Also, Could you please help me understand the logic with going this way
1 of 1 people found this helpful
You are welcome Madhuri.
You may not be able to achieve the result without joining with the Dates dataset because you need to look at the current date/month along with all the prior dates/months. So, if you have seen the join condition, the single record of a specific created date needs to be considered as available for all future dates, and only then you can count it on a future date.