Power Query - Calcolare la competenza economica dei costi L’argomento è risolto

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

Utilizzo_prof_Excel
Messaggi: 27 | Topic creati
Iscritto il: gio 16 lug 2020, 10:55
Luogo: Torino
Ringraziato: 16 volte
Contatta:

Power Query - Calcolare la competenza economica dei costi

Messaggio da Utilizzo_prof_Excel »

Ciao a tutti,
per chi si occupa di contabilità e controllo di gestione spesso capita di dover affrontare il "problema" della competenza economica dei costi, ossia un costo che deve essere "spalmato" su un periodo di tempo (l'esempio classico sono le assicurazioni che di solito coprono un anno intero) e dovendo analizzare il conto economico per ad una data devono essere considerati solo per una "quota" (tipicamente la quota fuori dal periodo oggetto di analisi si definisce rateo o risconto).

In questo video spiego come è possibile utilizzare power query per ottenere un conto economico per competenza partendo dalle registrazioni che riportano il periodo di competenza:



Indipendentemente che vi occupiate di controllo di gestione, si tratta comunque di un bel esercizio da fare con power query, che permette di acquisire dimestichezza con diverse funzionalità.

Buon lavoro a tutti! :wave:



emapas
Messaggi: 2 | Topic creati
Iscritto il: gio 23 lug 2020, 9:54
Contatta:

Power Query - Calcolare la competenza economica dei costi

Messaggio da emapas »

Ciao, un modo alternativo per far fare tutto il lavoro a Power Query potrebbe essere quello di calcolare i giorni fino alla data di fine anno del periodo "A" e a partire dalla data di inizio anno del periodo "DA". In questo modo la tabella risultante non crescerebbe di righe (con il tuo sistema cresce di tante righe quanti sono i giorni del periodo di competenza dei costi) e non si dovrebbe usare la pivot per aggregare i dati:

Codice: Seleziona tutto

let
    Origine = Excel.CurrentWorkbook(){[Name="Tabella1"]}[Content],
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Registrazione", type date}, {"Conto", type text}, {"Da", type date}, {"A", type date}, {"Importo", type number}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "Competenza gg Y", each Duration.Days(Date.EndOfYear([Da])-[Da])+1, type number),
    #"Colonna condizionale aggiunta" = Table.AddColumn(#"Aggiunta colonna personalizzata", "Competenza gg Y+1", each if (Date.Year([A]) = Date.Year([Da])) then 0 else Duration.Days([A]-Date.StartOfYear([A]))+1, type number),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Colonna condizionale aggiunta", "Competenza Y", each [Importo]*[Competenza gg Y]/([Competenza gg Y]+[#"Competenza gg Y+1"]), type number),
    #"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "Competenza Y+1", each [Importo]*[#"Competenza gg Y+1"]/([Competenza gg Y]+[#"Competenza gg Y+1"]), type number),
    #"Rimosse colonne" = Table.RemoveColumns(#"Aggiunta colonna personalizzata2",{"Competenza gg Y", "Competenza gg Y+1"})
in
    #"Rimosse colonne"
Allego qui il foglio di lavoro che include anche il la mia query caricata come tabella sul Foglio 2.
Ciao
Emanuele
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:

Power Query - Calcolare la competenza economica dei costi

Messaggio da Enrico Galli »

Ciao a tutti,
Utilizzo_prof_Excel : grazie mille per la condivisione :)
emapas : grazie anche a te per l'esempio. Tuttavia devo dirti che, lavorando come hai fatto tu, ti limiti parecchio: il tuo file infatti prende in considerazione solo le competenze che si distribuiscono su 2 anni, e comunque aggrega gli importi solo per anno. Espandendo le righe giorno per giorno come ha fatto Emmanuele, invece, rendi il file completamente dinamico e aggregabile per settimane, mesi, trimestri, anni... etc., senza porre limiti all'intervallo Da-A. Se non si superano le decine di milioni di righe, grossi problemi di prestazioni non ce ne sono.
E' uno di quei casi in cui le tabelle pivot sono davvero insostituibili, meglio ancora se in versione "Power", con tabella calendario separata e in relazione: con questo setup non ci sono più limiti alle possibilità di analisi e confronti temporali che si possono ottenere! 8-)
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

domemarti93
Messaggi: 30 | Topic creati
Iscritto il: mar 11 ago 2020, 14:21
Ringraziato: 1 volta

Power Query - Calcolare la competenza economica dei costi

Messaggio da domemarti93 »

Ciao!
Sto provando a calcolare l'importo mensile, invece che giornaliero.
La cosa funziona bene nel momento in cui ho date di inizio al primo giorno del mese e date di fine all'ultimo giorno del mese.
Tuttavia nel momento in cui metto ad esempio come data di inizio il 14 del mese, non mi trovo più!
Qualche consiglio su come risolvere?
grazie mille a tutti
Allegati
competenza economica - mensile.xlsx
(22.72 KiB) Scaricato 49 volte
competenza economica - mensile.xlsx
(22.72 KiB) Scaricato 22 volte
competenza economica - mensile.xlsx
(22.72 KiB) Scaricato 22 volte
Avatar utente

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

Power Query - Calcolare la competenza economica dei costi

Messaggio da Andrea90 »

Ciao domemarti93,

Hai inviato 3 allegati uguali ?

Da quello che vedo, visto che non vuoi calcolare una data precisa di fine ti basta prendere la data di inizio e di fine periodo e tramite trasforma prendi il primo giorno della data indicata come ad esempio, per il campo [Da] (nel caso del 14/05/2020 ti riporterebbe il valore a 01/05/2020) e stessa cosa con il campo [a] (nel caso di 21/05/2020 ti riporterebbe il valore 31/05/20). Poi da li puoi eseguire i passaggi che hai fatto se hai visto che ti riportano il valore desiderato. La trasformazione che ti ho indicato ti consente di avere sempre una data di inizio che parte dal primo del mese, ed una data di fine che termina con l'ultimo giorno del mese indicato.

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

domemarti93
Messaggi: 30 | Topic creati
Iscritto il: mar 11 ago 2020, 14:21
Ringraziato: 1 volta

Power Query - Calcolare la competenza economica dei costi

Messaggio da domemarti93 »

Perfetto grazie

EmaMax
Messaggi: 2 | Topic creati
Iscritto il: gio 9 lug 2020, 10:00

Power Query - Calcolare la competenza economica dei costi

Messaggio da EmaMax »

Ciao a tutti,
come prevedeva nel post precedente Enrico Galli , dopo aver calcolato le competenze per giorno e seguito correttamente tutti i passaggi, ho superato le righe/contratti (disponibili?) ...infatti, excel mi restituisce un allert con il quale mi avvisa di aver superato il numero di righe gestibili.

A fronte di ciò, mi potete indicare cortesemente come risolvereste il problema (calcolo x mese?)?

Personalmente, pensavo di seguire questi passaggi:

1 duplicare le tabelle per tipologia di contratti;
2 fare eseguire i calcoli per determinare la competenza e economica per giorno;
3 creare in base a data scadenza, colonna con mese o ultimo giorno del mese;
4 raggruppare per mese;
5 aggregare tutte le tabelle con appending per ritornare ad avere una tabella sola
6 creare pivot

in alternativa andare in powerpivot tenendo separate le tabelle e relazionandole con un tracciato ponte con chiave univoca "tipologia contratto"

Vi ringrazio in anticipo
Max
Rispondi