Allocazione Spese/Ricavi

Moderatore: Utilizzo_prof_Excel

Rispondi
Avatar utente

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

Allocazione Spese/Ricavi

Messaggio da Andrea90 »

Buonasera a tutti gli utenti del forum :wave:,

In settimana è uscita una discussione riguardante la fattibilità di suddividere un certo quantitativo per i mesi di competenza.
Sicuramente esistono diversi modi per riuscire ad ottenere il risultato, e non solo tramite PowerQuery, ma quello che propongo stasera riguarda proprio questo strumento.

La sintassi utilizzata non è complessa, e si riesce ad ottenere tutto tramite interfaccia e qualche colonna personalizzata.

Partiamo con ordine mostrando quella che è la base dati:

Immagine

Abbiamo una data di inizio e di fine periodo, ed un importo da distribuire sulla base del numero dei giorni presenti in ciascun mese. Le date possono essere a cavallo di mesi, ma anche a cavallo di anni, come si può vedere dall'ultima riga.

Codice: Seleziona tutto

let
    Origine = Excel.CurrentWorkbook(){[Name="Dati"]}[Content],
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Client", type text}, {"Sale", type number}, {"Start Date", type date}, {"End Date", type date}}),
    #"Date List" = Table.AddColumn(#"Modificato tipo", "Date_List", each List.Dates([Start Date], Duration.Days([End Date]-[Start Date])+1, #duration(1, 0, 0, 0))),
    #"Tabella Date_List espansa" = Table.ExpandListColumn(#"Date List", "Date_List"),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Tabella Date_List espansa",{{"Date_List", type date}}),
    #"Inserito mese" = Table.AddColumn(#"Modificato tipo1", "Mese", each Date.Month([Date_List]), Int64.Type),
    #"Inserita fine del mese" = Table.AddColumn(#"Inserito mese", "Fine del mese", each Date.EndOfMonth([Date_List]), type date),
    #"Raggruppate righe" = Table.Group(#"Inserita fine del mese", {"Client", "Sale", "Start Date", "End Date", "Mese", "Fine del mese"}, {{"Tot Days", each Table.RowCount(_), Int64.Type}}),
    #"Start Date" = Table.AddColumn(#"Raggruppate righe", "New_Date_Start", each if Date.Month([Start Date]) = [Mese] and Date.Year([Start Date]) = Date.Year([Fine del mese]) then [Start Date] else Date.StartOfMonth([Fine del mese]), type date),
    #"End Date" = Table.AddColumn(#"Start Date", "New_Date_End", each if Date.Month([End Date]) = [Mese] and Date.Year([End Date]) = Date.Year([Fine del mese]) then [End Date] else [Fine del mese], type date),
    #"Rimosse altre colonne" = Table.SelectColumns(#"End Date",{"Client", "Sale", "Tot Days", "New_Date_Start", "New_Date_End"}),
    #"Riordinate colonne" = Table.ReorderColumns(#"Rimosse altre colonne",{"Client", "Sale", "New_Date_Start", "New_Date_End", "Tot Days"})
in
    #"Riordinate colonne"
Andando per ordine, vediamo gli step principali (inserisco solo quelli più rilevanti):

1) #"Date List" --> Utilizzo la formula List.Dates (Guida) che risulta sempre molto utile quando si lavora con le date. A questa formula serve il riferimento ad una data di partenza, in questo caso [Start Date], un numero di "step" da effettuare a partire da quest'ultima, e per farlo ho utilizzato la formula Duration.Days (Guida) la quale mi restituisce il numero dei giorni compresi tra due date. Ho aggiunto +1 in maniera tale da poter considerare entrambi i giorni che delimitano il periodo. Infine ho definito il tipo di step, in questo caso mi serviva che fosse espresso in giorni e dunque ho utilizzato la formula #duration(1, 0, 0, 0) dove 1 sta per 1 giorno, poi 0 ore, 0 minuti e 0 secondi.

2) Ho espanso il nuovo campo, ovvero un tipo lista in maniera tale da avere su ciascuna riga uno specifico giorno compreso all'interno del range di riferimento. Essendo un valore data sono andato anche a calcolare il numero del mese, e la data di fine mese, poiché mi servirà in uno dei passaggi seguenti

3) #"Raggruppate righe" --> Con questo passaggio ho raggruppato le singole righe considerando i valori distinti per cliente ottenendo così il numero dei giorni per ciascun mese. Il raggruppamento è stato definito in questo modo:

Immagine

4) Start Date / End Date --> A questo punto il file è quasi pronto, devo solo andare a calcolare le date da prendere come riferimento per definire gli estremi di ciascun mese. Ho utilizzato questa formula per la data di inizio (quella della data di fine è molto simile):

Codice: Seleziona tutto

if Date.Month([Start Date]) = [Mese] and Date.Year([Start Date]) = Date.Year([Fine del mese]) then [Start Date] else Date.StartOfMonth([Fine del mese])
La logica è la seguente:
"" se Il mese della Data di Inizio è uguale al valore del mese corrente e se l'anno della data di inizio è uguale a quello della data di fine mese,
allora abbiamo come data, quella di partenza (Start Date), altrimenti vuol dire che devo prendere la data di inizio del mese di riferimento,
ottenuto tramite la formula Date.StartOfMonth().
""

Lascio comunque il file allegato per chi volesse dare un'occhiata alle formule e agli step che vengono eseguiti dalla query.

I giorni di riferimento poi possono ovviamente essere modificati sulla base delle esigenze dell'utente, ad esempio per il cliente Delta, la prima data è il 30/11 che rappresenta anche la data di fine mese. La formula riporta comunque 1 come valore, ma nulla vieta di poter creare una colonna personalizzata che imposti uguale a 0 il valore dei giorni, se la data di inizio coincide anche con la data di fine.

Spero che questo breve tutorial possa ritornare utile, così come anche le formule che sono state utilizzate.

Grazie per l'attenzione e una buona serata :wave:

Andrea
Allegati
Test_Allocazione.xlsx
(18.62 KiB) Scaricato 145 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

Gennaro_64
Messaggi: 107 | Topic creati
Iscritto il: dom 11 apr 2021, 12:47
Ringraziato: 5 volte

Allocazione Spese/Ricavi

Messaggio da Gennaro_64 »

Ottimo tutorial.
Rispondi