date

[Syndicate_Admin]

Good afternoon, I have a situation.

I need to form the initial balance, which responds to the final balance of "End Balance Date + 1 or next day skilled

example: the starting balance at 03-03, is to the final balance of 02-03

In this case to the table add an Initial Balance Date field, and use the formula "nextday", but it does not work, because to the date that corresponds to 26-02, it leaves it blank, since it does not find in this table the date 27.

Also use, Final Balance (date) +1, but also not, since what I intend, is that 26-02 corresponds to the next day of skill, but precisely that of the table, in this case 01-03.

It doesn't serve to filter by weekend, because I can have holidays or non-working, not just weekends.

Source table:

TablaSaldo Final

Identification	Idcuentabancaria	date	Balance End	number
73517	2	02/03/2021	$ 39.843.865,73	10003272284
73424	2	1/3/2021	$ 20.604.309,17	10003272284
73362	2	26/2/2021	$ 41.442.774,33	10003272284
73335	2	25/2/2021	$ 32.222.477,29	10003272284

Expected Result

Final Balance Table

Identification	Idcuentabancaria	Date Final balance	Balance End	number	Date Initial Balance	Starting Balance
73517	2	02/03/2021	$ 39.843.865,73	10003272284	3/3/2021	$ 39.843.865,73
73424	2	1/3/2021	$ 20.604.309,17	10003272284	02/03/2021	$ 20.604.309,17
73362	2	26/2/2021	$ 41.442.774,33	10003272284	1/3/2021	$ 41.442.774,33
73335	2	25/2/2021	$ 32.222.477,29	10003272284	26/2/2021	$ 32.222.477,29

[v-yetao1-msft · Accepted solution]

Hi @Syndicate_Admin 

Please correct me if I wrongly understood your question.

As you said ,if the next day of final date is weekdays , then return the date of next day .If the next day of final date is weekend ,then return the date of next Monday .

I create a measure like this:

Start Date =

var _backday=WEEKDAY(SELECTEDVALUE('Saldo Final'[cuentabancariadate]),2)

return IF(_backday<5,SELECTEDVALUE('Saldo Final'[cuentabancariadate])+1,SELECTEDVALUE('Saldo Final'[cuentabancariadate])+3)

I mark [final date] with 1-7, 1 for Monday, 2 for Tuesday, and so on. So when date is less than 5, its next day is always a working day .Then you can +1 to the date, otherwise +3 .

The effect is as shown :

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.