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!
Power Query - Calcolare la competenza economica dei costi L’argomento è risolto
Moderatore: Utilizzo_prof_Excel
-
- Messaggi: 27 | Topic creati
- Iscritto il: gio 16 lug 2020, 10:55
- Luogo: Torino
- Ringraziato: 16 volte
- Contatta:
-
- Messaggi: 2 | Topic creati
- Iscritto il: gio 23 lug 2020, 9:54
- Contatta:
Power Query - Calcolare la competenza economica dei costi
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:
Allego qui il foglio di lavoro che include anche il la mia query caricata come tabella sul Foglio 2.
Ciao
Emanuele
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"
Ciao
Emanuele
-
- 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
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!
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!
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
Link utili: I nostri tutorial | Come inserire: Immagini - Codice - Risolto
Se il forum ti è stato utile, considera di supportarlo con una libera donazione
-
- Messaggi: 30 | Topic creati
- Iscritto il: mar 11 ago 2020, 14:21
- Ringraziato: 1 volta
Power Query - Calcolare la competenza economica dei costi
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
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
-
- 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
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
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
-
- Messaggi: 30 | Topic creati
- Iscritto il: mar 11 ago 2020, 14:21
- Ringraziato: 1 volta
-
- Messaggi: 2 | Topic creati
- Iscritto il: gio 9 lug 2020, 10:00
Power Query - Calcolare la competenza economica dei costi
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
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