Stima Data Attesa e Lead Time su Power Query

Non è propriamente un programma di BI, ma nelle sue versioni più recenti anche Excel può fare grandi cose!

Moderatore: Utilizzo_prof_Excel

Rispondi
Avatar utente

Fabio07
Messaggi: 6 | Topic creati
Iscritto il: sab 20 mar 2021, 16:44
Ringraziato: 1 volta
Contatta:

Stima Data Attesa e Lead Time su Power Query

Messaggio da Fabio07 »

Ciao a tutti,

Ringrazio anticipatamente chi avrà voglia o modo di aiutarmi a comprendere i tasselli mancanti.
Nel mio lavoro, dovendo analizzare processi di natura logistica, mi trovo spesso a lavorare con degli ETA (intese come date attese di processo, lavorazione, evasione o consegna di un ordine), piuttosto che con analisi a livello di Lead Time in giorni lavorativi tra due date. Questi temi, facilmente smarcati su Excel con le apposite funzioni customizzabili, mi stanno creando notevoli grattacapi nel mio tentativo di spostarle su BI.

- Calcolo ETA: la mia necessità è quella, a partire da una data di partenza inserita in una colonna e da una colonna "promise" contenente un num. di giorni per la lavorazione, stimare una data stimata (per intenderci, funzione GIORNO.LAVORATIVO). La funzione Date.AddDays di Power Query purtroppo non esclude weekends e festività. Cercando una soluzione, ho trovato un video molto ben fatto di Lodovico D'Incau, che però non mi pareva rispondesse al mio specifico fabbisogno, e ho trovato un codice di una funzione customizzata online, che vi riporto sotto. Oltre a non riuscire ancora a non comprendere a fondo codice, mi crea alcuni problemi in stima date; ad esempio:

1) Se inserisco data Venerdì e 1 giorno da aggiungere, mi stima Martedì (invece avrei bisogno stimasse il Lunedì).
2) Non tiene conto delle Holidays nazionali (forse va aggiunta una riga DeleteHolidays=List.Select + List of Dates da una certa tabella e aggiungere il riferimento alla riga di codice successiva?

Utente Imke F community Power BI:

Codice: Seleziona tutto

let
  func = (StartDate as date, WorkDays as number) =>
    let
      WorkDays2 = (WorkDays * 2) + 7,
      startDate = 
        if Date.DayOfWeek(StartDate) = 5 then
          Date.AddDays(StartDate, 2)
        else if Date.DayOfWeek(StartDate) = 6 then
          Date.AddDays(StartDate, 1)
        else
          StartDate,
      ListOfDates = List.Dates(startDate, WorkDays2, #duration(1, 0, 0, 0)),
      DeleteWeekends = List.Select(ListOfDates, each Date.DayOfWeek(_, 1) < 5),
      WorkDate = List.Range(DeleteWeekends, WorkDays, 1),
      Result = WorkDate{0}
    in
      Result
in
  func

- Calcolo Lead Time: Non sono riuscito a trovare una soluzione con linguaggio M e Power Query, pertanto è una colonna che calcolo nello specifico con DAX su Power BI caricando un calendario con giorni lavorativi=1, weekends e Holidays = 0 e usando SUMX con FILTER in combinazione e sommando gli 1 tra le due date.

Se qualcuno sapesse aiutarmi a risolvere questo problema, o sapesse suggerirmi qualche soluzione già trovata in forum o qualche video che possa risolvere questo problema gliene sarei gratissimo. Ho cercato al meglio delle mie possibilità attuali di venire a capo di queste esigenze, ma ho finito le idee :(

Già che ci sono, vi chiederei un parere se secondo voi l'inserimento di una colonna ad hoc su Power Query e quindi un valore per ogni riga tabellare sia il modo più consigliato per determinare questi due valori.

Spero gradualmente di riuscire a rafforzare le mie competenze, migliorando e poter fornire poi anche io contributi di utilità in forum.

Grazie mille e chiedo scusa per il lungo messaggio.

Buon fine settimana,

Fabio


Avatar utente

Andrea90
Messaggi: 2193 | Topic creati
Iscritto il: dom 28 giu 2020, 19:41
Luogo: Bologna
Ringraziato: 666 volte
Contatta:

Stima Data Attesa e Lead Time su Power Query

Messaggio da Andrea90 »

Ciao Fabio07,

Se utilizzi PowerBI allora io sposterei li il calcolo del tuo ETA, poiché oltre a strutturare una tabella calendario, e ad avere già disponibili le Time Intelligence Function di DAX puoi anche prevedere una tabella con le festività nazionali in maniera tale da tenerne conto. A quel punto ti crei una nuova colonna calcolata che partendo dalla tua data di partenza sommerà i giorni da te indicati (promise) escludendo quelli che sono festivi e che rientrano nell'elenco delle festività nazionali.

A presto,
Andrea
Se hai gradito l'aiuto che hai ricevuto considera di contribuire alle spese per il mantenimento del forum facendo una libera DONAZIONE --> Link

Ricordarsi di segnare come "RISOLTE" le discussioni per le quali si è ricevuto un feedback positivo. Per vedere come fare --> Link
Avatar utente

Autore del topic
Fabio07
Messaggi: 6 | Topic creati
Iscritto il: sab 20 mar 2021, 16:44
Ringraziato: 1 volta
Contatta:

Stima Data Attesa e Lead Time su Power Query

Messaggio da Fabio07 »

Ciao Andrea90 ,

Grazie mille per il riscontro.
Sono un utilizzatore alle prime armi anche di Power BI, diciamo che sto portando avanti entrambe le cose, poiché per alcune fruizioni trovo molto utile avere la sensibilità di excel in forma tabellare, ma per lavoro diciamo che mi è richiesto di lavorare anche con dashboards per la parte più "visual".
Quindi suggerisci di utilizzare DAX per risolvere questo quesito?

Per il punto 2, il calcolo Lead Time, sto utilizzando la seguente formula:

Codice: Seleziona tutto

=
IF (
    [Shipping Date] = [Actual Delivery Date],
    0,
    SUMX (
        FILTER (
            'Calendar',
            'Calendar'[Data] > 'Ordini'[Shipping Date]
                && 'Calendar'[Data] <= 'Ordini'[Actual Delivery Date]
        ),
        'Calendar'[Is Working Day]
    )
)
Dove "is Working Day" è colonna di un calendario con 1 per giorni lavorativi e 0 per weekend e festivi.
Suggerisci che sia possibile riadattare questa formula per ottenere l'inverso, ovvero a partire da 'Calendar'[Data] aggiungere un certo ammontare di "1", fino a restituire la data corrispondente? Per caso è da utilizzarsi la funzione DATEADD, DATEADD(<dates>,<number_of_intervals>,<interval>), lavorando tipo con FILTER su <number_of_intervals>?

Grazie mille e buona serata,

Fabio
Avatar utente

Andrea90
Messaggi: 2193 | Topic creati
Iscritto il: dom 28 giu 2020, 19:41
Luogo: Bologna
Ringraziato: 666 volte
Contatta:

Stima Data Attesa e Lead Time su Power Query

Messaggio da Andrea90 »

Ciao Fabio,

Comincerei col dire che senza vedere un modello dati demo sotto faccio fatica a darti una mano.

Quello che vuoi far tu è:

1) Ho una data di inizio ed una di fine e devo calcolare quanti giorni lavorativi intercorrono tra le due escludendo quindi i weekend e le festività

oppure

2) Ho una data di inizio e devo aggiungere N giornate lavorative trovando una data di fine, escludendo però i weekend e le festività ?

oppure un'altra cosa ancora?

A presto,
Andrea
Se hai gradito l'aiuto che hai ricevuto considera di contribuire alle spese per il mantenimento del forum facendo una libera DONAZIONE --> Link

Ricordarsi di segnare come "RISOLTE" le discussioni per le quali si è ricevuto un feedback positivo. Per vedere come fare --> Link
Avatar utente

Autore del topic
Fabio07
Messaggi: 6 | Topic creati
Iscritto il: sab 20 mar 2021, 16:44
Ringraziato: 1 volta
Contatta:

Stima Data Attesa e Lead Time su Power Query

Messaggio da Fabio07 »

Ciao Andrea90 ,

Grazie per il riscontro.
Non riesco a trovare una soluzione al punto 2)
Andrea90 ha scritto: sab 20 mar 2021, 19:56 2) Ho una data di inizio e devo aggiungere N giornate lavorative trovando una data di fine, escludendo però i weekend e le festività ?
Il punto 1 l'ho risolto con la formula DAX precedentemente citata, mi chiedo se sia rilavorabile quella in qualche modo, essendo l'obiettivo differente, ma i dati utilizzati i medesimi.

Ho un dataset con N colonne, per cui l'identificativo è un numero ordine, tra le quali appunto c'è una data di spedizione (o Shipping Date, come in formula). Ad ogni ordine, in base a certe specifiche, associo una "Promise" di consegna in giorni lavorativi. Il mio obiettivo è pertanto riuscire a stimare la data futura rispetto alla Shipping Date. Legando in Relazione Shipping Date a Calendar, trovare quindi la data che sia tanti "1" giorni lavorativi distante dalla Shipping Date, restituita come corrispondente.

Mi scuso se a sto punto probabilmente siamo finiti off-topic, in quanto partiti da Power Query e finiti a parlare di DAX, anche se prob una simile formula potrebbe essere utilizzata su Power Pivot.

Grazie mille,

Fabio
Avatar utente

Andrea90
Messaggi: 2193 | Topic creati
Iscritto il: dom 28 giu 2020, 19:41
Luogo: Bologna
Ringraziato: 666 volte
Contatta:

Stima Data Attesa e Lead Time su Power Query

Messaggio da Andrea90 »

Ciao Fabio07,

Io ho provato cosi:

Codice: Seleziona tutto

ETA = 
VAR TempTable =
    TOPN (
        Ordini1[Promesse],
        FILTER (
            Calendario2,
            Calendario2[Data] >= Ordini1[Data]
                && Calendario2[Festivo] = 1
        )
    )
VAR Result =
    MAXX ( TempTable, Calendario2[Data] )
RETURN
    Result
Ti lascio un file allegato dove trovi una demo (la prossima volta però dovrai essere tu a prepararlo, non serve un data model completo bastano due tabelle esemplificative per spiegare il problema che si sta riscontrando).

Ho aggiunto una tabella Calendario dove troverai una colonna con 0 o 1 a seconda che sia lavorativo oppure no (poi qui lascio a te costruirti la logica per il calcolo automatico delle festività nazionali, oltre che dei weekend).

Prova a vedere se è il risultato desiderato (alcuni giorni di maggio 2021 li ho messi a 0 anche se non erano festività per fare una prova).
P.s. Il giorno di inizio l'ho considerato parte del calcolo. Puoi cambiarne anche la logica

Immagine

A presto,
Andrea
Allegati
Date_ETA.pbix
(31.71 KiB) Scaricato 34 volte
Se hai gradito l'aiuto che hai ricevuto considera di contribuire alle spese per il mantenimento del forum facendo una libera DONAZIONE --> Link

Ricordarsi di segnare come "RISOLTE" le discussioni per le quali si è ricevuto un feedback positivo. Per vedere come fare --> Link
Avatar utente

Enrico Galli
Messaggi: 890 | Topic creati
Iscritto il: dom 28 giu 2020, 19:03
Luogo: San Giovanni in Persiceto (BO)
Ringraziato: 325 volte
Contatta:

Stima Data Attesa e Lead Time su Power Query

Messaggio da Enrico Galli »

Ciao e grazie mille per il supporto! :thumbup:

Innanzi tutto ti segnalo che il forum può gestire la sintassi di vari codici tra cui DAX e Power Query: per utilizzare questa funzione ti rimando a questo annuncio.

La funzione personalizzata di Imke Feldmann che hai trovato online, a me funziona correttamente: puoi fare un esempio di utilizzo e farci capire come mai non ti dà il risultato atteso?
Inoltre, come da te giustamente ipotizzato, per raffinarla dovresti aggiungere un'ulteriore riga di rimozione date, facendola diventare ad esempio così:
Fabio07 ha scritto: sab 20 mar 2021, 17:18

Codice: Seleziona tutto

let
  func = (StartDate as date, WorkDays as number) =>
    let
      WorkDays2 = (WorkDays * 2) + 7,
      startDate = 
        if Date.DayOfWeek(StartDate) = 5 then
          Date.AddDays(StartDate, 2)
        else if Date.DayOfWeek(StartDate) = 6 then
          Date.AddDays(StartDate, 1)
        else
          StartDate,
      ListOfDates = List.Dates(startDate, WorkDays2, #duration(1, 0, 0, 0)),
      DeleteWeekends = List.Select(ListOfDates, each Date.DayOfWeek(_, 1) < 5),
      DeleteHolidays = List.Remove(DeleteWeekends, Holidays),
      WorkDate = List.Range(DeleteHolidays, WorkDays, 1),
      Result = WorkDate{0}
    in
      Result
in
  func
dove Holidays è una lista di date che rappresentano i festivi.
Enrico Galli
Link utili: I nostri tutorial | Come inserire: Immagini - Codice - Risolto
Se il forum ti è stato utile, considera di supportarlo con una libera donazione
Avatar utente

Autore del topic
Fabio07
Messaggi: 6 | Topic creati
Iscritto il: sab 20 mar 2021, 16:44
Ringraziato: 1 volta
Contatta:

Stima Data Attesa e Lead Time su Power Query

Messaggio da Fabio07 »

Ciao Andrea90 ed Enrico Galli,

Grazie mille per il vostro supporto.
Intanto vi ringrazio tanto per l'aiuto, mi prendo un congruo tempo per verificare la soluzione suggerita da Andrea90 e cosa abbia sbagliato nell'applicazione della formula personalizzata di Imke Feldmann (probabilmente a sto punto ho commesso un errore io). Proverò ad integrare anche la info Holidays, come suggerito da Enrico Galli. Cercherò di valutare anche quale opzione sia preferibile per il mio modello dati.

Torno da voi il prima possibile.

Buona Domenica,

Fabio
Avatar utente

Autore del topic
Fabio07
Messaggi: 6 | Topic creati
Iscritto il: sab 20 mar 2021, 16:44
Ringraziato: 1 volta
Contatta:

Stima Data Attesa e Lead Time su Power Query

Messaggio da Fabio07 »

Ciao a tutti,

Scusate per la tardiva risposta, ho preso del tempo per lavorare sulle soluzioni da voi proposte, nonché cercare di capirle e non solo eseguirle.

Enrico Galli, in effetti ti confermo che la funzione precedentemente citata funziona, ma non sto riuscendo ad inserire le "Holidays"; ho creato elenco date e cercato di creare un passaggio in cui sfiltrasse dall'elenco date in passaggio precedente (Deleteweekends, che a sua volta lo interpreto come elenco date al netto dei giorni 6 e 7, weekends), le date inserite nell'elenco. Per farlo, ho pensato di usare la funzione List.RemoveItems, ma ammetto senza molto successo. Sospetto possa mancare un passaggio in cui trasformo la tabella delle date inserite ed importate in una lista da sfiltrare, ma non ne sono così sicuro. Riporto sotto codice, che mi restituisce errore poi nel passaggio List.Range(DeleteHolidays...).

= (StartDate as date, WorkDays as number) =>
let
WorkDays2 = (WorkDays * 2) + 7,
startDate =
if Date.DayOfWeek(StartDate) = 5 then
Date.AddDays(StartDate, 2)
else if Date.DayOfWeek(StartDate) = 6 then
Date.AddDays(StartDate, 1)
else
StartDate,
ListOfDates = List.Dates(startDate, WorkDays2, #duration(1, 0, 0, 0)),
DeleteWeekends = List.Select(ListOfDates, each Date.DayOfWeek(_, 1) < 5),
DeleteHolidays = List.RemoveItems(DeleteWeekends, Holidays),
WorkDate = List.Range(DeleteHolidays, WorkDays, 1),
Result = WorkDate{0}
in
Result


Andrea90, ho riallegato un BI con le impostazioni che uso io. Ho importato il mio "Calendar", con data, Weekday e tipo di giorno (1 per i lavorativi, 0 per WE e festivi), nonché il DB Report 1 con un estratto di ordini (Num. Ordine, Data e giorno ordine, Promise al cliente ecc.). Ho poi riadattato la tua formula DAX al mio DB e alla relazione tra i campi del mio modello, tuttavia non mi tornano le date. Ho sbagliato qualcosa secondo te? Il mio obiettivo in sostanza è arrivare a un ETA di questo tipo:

- Order Date 01/04, Promise (Working days) = 1 --> ETA 02/04
- Order Date 02/04, Promise (Working days) = 1 --> ETA 06/04
- Order Date 01/04, Promise (Working days) = 2 --> ETA 06/04
- Order Date 02/04, Promise (Working days) = 4 --> ETA 09/04

E così via.

Mi scuso ancora per il riscontro tardivo, ma ripeto, sto cercando comunque non solo di risolvere questa esigenza, quanto anche di approfondire e capire meglio le soluzioni da voi proposte.

Grazie mille e un augurio di Buona Pasquetta!

Fabio
Allegati
Date_ETA.pbix
Rilavorazione file di Andrea90
(57.43 KiB) Scaricato 18 volte
Avatar utente

Andrea90
Messaggi: 2193 | Topic creati
Iscritto il: dom 28 giu 2020, 19:41
Luogo: Bologna
Ringraziato: 666 volte
Contatta:

Stima Data Attesa e Lead Time su Power Query

Messaggio da Andrea90 »

Ciao Fabio07,
Fabio07 ha scritto: lun 5 apr 2021, 13:12 - Order Date 02/04, Promise (Working days) = 1 --> ETA 06/04
Perché dal 02/04 con 1 giorno lavorativo metti 06/04 ? Non dovrebbe essere 05/04 visto che quel giorno ha valore 1 nel campo [Festivo] della tabella Calendario2?
Andrea90 ha scritto: sab 20 mar 2021, 20:38 P.s. Il giorno di inizio l'ho considerato parte del calcolo. Puoi cambiarne anche la logica
Come ti dicevo, se non vuoi che il primo giorno venga considerato, all'interno della formula FILTER() invece che usare >= devi utilizzare solo >

Per il resto mi sembra che tu abbia fatto qualche refuso con i nomi dei vari campi/tabelle. Hai aggiunto una nuova tabella Calendario ad esempio, ma nel file che hai girato vedo ancora dei riferimenti alla tabella Calendar che avevo creato io.

A presto,
Andrea
Se hai gradito l'aiuto che hai ricevuto considera di contribuire alle spese per il mantenimento del forum facendo una libera DONAZIONE --> Link

Ricordarsi di segnare come "RISOLTE" le discussioni per le quali si è ricevuto un feedback positivo. Per vedere come fare --> Link
Rispondi