Dynamic formula (variable)

[Felipe_Amancio]

Hi masters,

I would like to know if it is possible to change the number highlighted below (under Formula) as the months goes by.

The example below is how the formula should be in August as 243 is the total number of days year to date in August.

In September, I would like the number to change automatically to 273 (243 + 30 days of September).

Is there a way to get the "243" updated dynamically every month? ...For example, by typing a number or date in the canvas so it gets linked to the formula automatically? Any other creative way?

([JAN-22]*31+[FEB-22]*28+[MAR-22]*31+[APR-22]*30+[MAY-22]*31+[JUN-22]*30+[JUL-22]*31+[AUG-22]*31+[SEP-22]*30+[OCT-22]*31+[NOV-22]*30+[DEC-22]*31)/243

The goal of the above formula is to calculate the weighted average balance YTD

Thanks

[Felipe_Ribeir0 · Accepted solution]

Hi @Felipe_Amancio 

One way of doing this, take a look at the attached workflow.

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[flying008]

Hi, @Felipe_Amancio 

1- If your number base on today, please use the formula: (the result is 304 )

Tonumber(DateTimeFormat(DateTimeParse(Tostring(DateTimeFormat(DateTimeAdd(DateTimeToday(),1,"Months"),"%Y-%m")) + "-01","%Y-%m-%d"),"%j"))-1

2- If your number base on latest month of today like "2022-09", please use the formula: (the result is 273 )

Tonumber(DateTimeFormat(DateTimeParse(Tostring(DateTimeFormat(DateTimeAdd(DateTimeToday(),0,"Months"),"%Y-%m")) + "-01","%Y-%m-%d"),"%j"))-1

3- If your number base on specify date string like "2022-10-10", please use the formula: (the result is 304 )

Tonumber(DateTimeFormat(DateTimeParse(Tostring(DateTimeFormat(DateTimeAdd("2022-10-10",1,"Months"),"%Y-%m")) + "-01","%Y-%m-%d"),"%j"))-1

4- Above all formulas are automatic for you want output.

5- You can put it in your expression like:

([JAN-22]*31+[FEB-22]*28+[MAR-22]*31+[APR-22]*30+[MAY-22]*31+[JUN-22]*30+[JUL-22]*31+[AUG-22]*31+[SEP-22]*30+[OCT-22]*31+[NOV-22]*30+[DEC-22]*31)/ Tonumber(DateTimeFormat(DateTimeLastOfMonth(),"%j"))

*******

So, If the formulas can help you get your want, please mark it is a solution and give a like to me. 😁

[grazitti_sapna]

@Felipe_Amancio , Here is another way of doing this.

Thanks