return the value based on a specific date to be used in a formula afterwards

[nkazi01]

hi, my excel file is getting updated daily. I want to create a calculated field using the data from the last day of the previous quarter. So in this case, the data from 2022-06-30

this is what the data looks like

date	total	QTD
06/29/2022	785785	0.25%
06/30/2022	756786	0.35%
......................................	...........................	................................
08/11/2022	25254	0.03%

QTD formula = daily Total / (Total from last quarter -1) = 25254 / (756786 -1) = 0.03

after some digging around I found that I can use this formula to get the last day of the previous quarter. And I know i can use multi-row formula tool to do row based calculations. I just need help figuring out how to use the below formula to get the value for that date from QTD column, and implement it into the multi-row formula.  

DateTimeAdd(
    Left(DateTimeToday(),5)+
    PadLeft(ToString(3*Floor((DateTimeMonth(DateTimeToday())-1)/3)+1,0),2,"0")+
    "-01",-1,"days")

Unless I am completely wrong and if there is a better/easier way to do this, then please point me in the right direction. 

[trsali]

@nkazi01 May I suggest a different approach? Are you trying to find the QTD of only the new dates daily, or do you need to find the QTD for all historical dates too?

[nkazi01]

hi @trsali so daily the QTD is calculated for that day only. So for today it will be (today's total / last quarter end total -1). Tomorrow's will use tomorrow's total as the numerator. And so on and so forth until sept 30. And from Oct 1st it will use the total for sept 30 in denominator... until december 31st. 

[trsali]

@nkazi01 Does the attached workflow help you with your problem?

[nkazi01]

Hi @trsali, not quite. I really appreciate your solution and it is quite helpful. But the issue i am facing now s that it is updating all the previous existing data for that field with the formula. But I only want to update the last row of data everyday. I cant use the sample tool to choose the last row because i need the previous data to update the current data. So the QTD for all other previous days is already updated, I just need to update today's data. Do you know how that can be done?