Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
94 | |
90 | |
79 | |
77 | |
71 |
User | Count |
---|---|
116 | |
106 | |
88 | |
64 | |
63 |