Dynamic formula (variable)

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

Help

Expression

Accepted answers

Felipe_Ribeir0

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.

Dynamic formula (variable).yxmd

All comments

Felipe_Ribeir0

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.

Dynamic formula (variable).yxmd

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

YTD.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703