Forum Discussion
date
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 |
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.
1 Reply
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.