Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Ciao a tutti,
devo calcolare una nuova data ordine sapendo che
_se la data ordine è stata inserita> 16.00 devo aggiungere 1 giorno.
_se la data calcolata ricade in un giorno festivo (sab. Dom. Ecc) devo posticiparla al primo giorno lavorativo.
Ho inserito una tabella calendario con i soli giorni lavorativi, con la funzione "SE" riesco a posticipare in un giorno i dati calcolati MA non riesco in nessun modo a posticipare la data al primo giorno utile lavorativo
Sono bloccato su questo punto, spero che qualcuno mi possa aiutare
grazie in anticipo!
Solved! Go to Solution.
HI @Denpowerbi,
I think you need to add conditions to if statement to check its weekday and hour. Please take a look at following calculate column formula if it meets your requirement:
NewDate =
IF (
HOUR ( [Datetime] ) >= 16,
IF (
WEEKDAY ( [Datetime], 2 ) >= 5,
DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 7
- WEEKDAY ( [Datetime], 2 ) + 1 ),
DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 1 )
),
IF (
WEEKDAY ( [Datetime], 2 ) >= 5,
DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 7
- WEEKDAY ( [Datetime], 2 ) + 1 ),
[Datetime]
)
)
Regards,
Xiaoxin Sheng
HI @Denpowerbi,
I think you need to add conditions to if statement to check its weekday and hour. Please take a look at following calculate column formula if it meets your requirement:
NewDate =
IF (
HOUR ( [Datetime] ) >= 16,
IF (
WEEKDAY ( [Datetime], 2 ) >= 5,
DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 7
- WEEKDAY ( [Datetime], 2 ) + 1 ),
DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 1 )
),
IF (
WEEKDAY ( [Datetime], 2 ) >= 5,
DATE ( YEAR ( [Datetime] ), MONTH ( [Datetime] ), DAY ( [Datetime] ) + 7
- WEEKDAY ( [Datetime], 2 ) + 1 ),
[Datetime]
)
)
Regards,
Xiaoxin Sheng
Thank you very much, I solved my problem with a small change to your suggestion.
NewDate
IF (
'OSC Sales Header :' [Now]> TIMEVALUE ("15:59:59");
IF (
WEEKDAY ([Date Order]; 2)> : 5;
DATE (YEAR([ORDER DATE]); MONTH ([Order Date]); DAY ([ORDER DATE]) : 7
- WEEKDAY ([Order Date]; 2) : 1);
DATE (YEAR ([Order Date]); MONTH ([Order Date]); DAY ([Order Date]) : 1)
);
"OSC Sales Header " [Order Date]
)
for checking the time you can use
Try in new column something like this
if([date].time > time(16,0,0) ,[date]+1,[date])
do add subtract working day see if my blog can help
Grazie, per controllare il tempo ok.
Ho guardato il tuo blog, molto interessante ma non riesco ancora a gestire i dati +1 solo nei giorni di lavoro.
User | Count |
---|---|
93 | |
85 | |
77 | |
68 | |
62 |
User | Count |
---|---|
113 | |
99 | |
96 | |
64 | |
58 |