Calcolo qty progressive in funzione di date fatture diverse per ogni distributore

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

Godwyn
Messaggi: 17 | Topic creati
Iscritto il: sab 5 dic 2020, 11:07
Luogo: Varese
Ringraziato: 4 volte

Calcolo qty progressive in funzione di date fatture diverse per ogni distributore

Messaggio da Godwyn »

Ciao ragazzi,

sono alle prese con una difficoltà che non riesco a risolvere, magari è una stupidata... :crazy:

Immaginare che abbia centinaia di distributori che inviano ogni mese le proprie fatture di vendita al cliente finale. Queste fatture vengono caricate su un gestionale per ricostruire il "mercato" di sell out.

Questi distributori non sono precisi, ovvero ciascuno invia i dati di vendita con tempistiche diverse. Alcuni distributori quindi avranno come ultima fattura maggio, altri giugno, altri novembre.

Vorrei calcolare le quantità vendute, per l'anno in corso e per l'anno precedente (stesso periodo!) in funzione dell'ultima data fattura ricevuta, che è diversa quindi per ogni attore.

A livello di data model ho una semplice fact table Sales collegata ad una tabella calendario e alle varie altre dimensioni (cliente/ distributore/venditore)

IO al momento avevo usato le classiche:

Codice: Seleziona tutto

QtàTotaleYTD = CALCULATE([QtàTotale];DATESYTD(TP_Calendario[Data]))

Codice: Seleziona tutto

QtàTotalePYTD = CALCULATE([QtàTotaleYTD];DATESYTD(DATEADD(TP_Calendario[Data];-1;YEAR)))
Ma naturalmente funzionano benissimo sul singolo distributore, ma non su tutti. E' fattibile ciò che chiedo?

Grazie mille


Avatar utente

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

Calcolo qty progressive in funzione di date fatture diverse per ogni distributore

Messaggio da Andrea90 »

Ciao Godwin,

Come diciamo sempre a tutti è necessario allegare un file con dei dati demo. Nel tuo caso basterebbe anche un file excel con la situazione di un paio di distributori finti in cui fai vedere che valore vorresti ottenere.

Questo perché così com’è scritta io non capisco cosa intendi dire quando sul singolo va bene, ma non su tutti.

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
Godwyn
Messaggi: 17 | Topic creati
Iscritto il: sab 5 dic 2020, 11:07
Luogo: Varese
Ringraziato: 4 volte

Calcolo qty progressive in funzione di date fatture diverse per ogni distributore

Messaggio da Godwyn »

Grazie mille come sempre Andrea,

ho provato rapidamente a ricostruire un excel con due degli scenari che mi trovo davanti e quello che invece vorrei vedere.

Non comprendo se ho fatto errori nelle formule DAX oppure se ciò che chiedo è impossibile. :)
Allegati
Cartel1.xlsx
File prova
(9.87 KiB) Scaricato 20 volte
Avatar utente

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

Calcolo qty progressive in funzione di date fatture diverse per ogni distributore

Messaggio da Andrea90 »

Ciao Godwyn,

La cosa credo che siapossibile, purtroppo ora non sono al pc, più tardi provo a scriverti se qualcun altro nel frattempo non ti ha ancora dato una mano.

Comunque la tua seconda formula non mi convince ad una prima occhiata ... per me l dateadd doveva andare all interno e non all’esterno... ho dato un’occhiata veloce,l però.

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

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

Calcolo qty progressive in funzione di date fatture diverse per ogni distributore

Messaggio da Andrea90 »

Ciao Godwyn,

Qualcosa di simile (vedi file allegato).

[#TotYTD]

Codice: Seleziona tutto

=VAR DateMax =
    MAXX ( Sales; Sales[Data] )
RETURN
    CALCULATE (
        [#TotValore];
        CALCULATETABLE ( DATESYTD ( 'Calendar'[Data] ); 'Calendar'[Data] <= DateMax )
    )
[#TotPYTD]

Codice: Seleziona tutto

=VAR DateMax =
    MAXX ( Sales; Sales[Data] )
RETURN
    CALCULATE (
        [#TotYTD];
        CALCULATETABLE (
            SAMEPERIODLASTYEAR ( 'Calendar'[Data] );
            'Calendar'[Data] <= DateMax
        )
    )
A presto,

Andrea
Allegati
TestDate.xlsx
(256.79 KiB) Scaricato 16 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

Autore del topic
Godwyn
Messaggi: 17 | Topic creati
Iscritto il: sab 5 dic 2020, 11:07
Luogo: Varese
Ringraziato: 4 volte

Calcolo qty progressive in funzione di date fatture diverse per ogni distributore

Messaggio da Godwyn »

Andrea funziona!!!! :)

Ora, invidia delle tue competenze a parte, cerco di comprendere, perchè sono testone e curioso cosa hai costruito e dove sbagliavo io :)

Il calcolo YTD mi torna:

Codice: Seleziona tutto

QtàTotaleYTD = CALCULATE([QtàTotale];DATESYTD(TP_Calendario[Data]))

Codice: Seleziona tutto

=VAR DateMax =
    MAXX ( Sales; Sales[Data] )
RETURN
    CALCULATE (
        [#TotValore];
        CALCULATETABLE ( DATESYTD ( 'Calendar'[Data] ); 'Calendar'[Data] <= DateMax )
    )
La tua è più "intelligente" perchè vedo che hai utilizzato una variabile che cerca prima il valore scalare (e immagino sia per questo che hai usato la CALCULATETABLE) massimo nella tabella Sales, campo data fattura. Ti chiedo, per curiosità se c'è qualche motivo specifico per la quale hai utilizzato questo metodo e in cosa il mio fosse scorretto o migliorabile.

Il calcolo PYTD è sulla falsa riga, a me con SAMEPERIODLASTYEAR non usciva questo risultato, ma immagino sia perchè cercassi il valore scalare massimo nella tabella calendario, può essere?

Grazie mille davvero, mi hai risolto un bel guaio!! :) :thumbup:
Avatar utente

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

Calcolo qty progressive in funzione di date fatture diverse per ogni distributore

Messaggio da Andrea90 »

Ciao Godwyn,

Quando lavori con le date la maggior parte delle volte quello che dovrai fare è:

CALCULATE( [EspressioneDaValutare], ListaDateUtili)

Quindi il primo argomento non è nient’altro che una formula di running total basata sull’anno. Il secondo argomento mi serve per poter dire a DAX di considerare non le date dell’anno corrente, ma di quello passato. SAMEPERIODLASTYEAR() non è nient’altro che una forma abbreviata di DATEADD con -1 e YEAR. Anche dal punto di vista di performance non c’è alcun miglioramento poiché DAX comunque utilizza DATEADD per valorizzarlo.

Quello che mi restituisce è una lista di date, il problema è che l’anno passato è un anno completo e pertanto arriva fino al 31/12, mentre quello corrente si ferma ad aprile. Per dire al sistema di considerare, si le date passate, ma solo quelle dei primi 4 mesi, ho calcolato la data max presente nella tabella Sales, e ho assegnato tale valore ad una variabile. Successivamente, essendo SAMEPERIODLASTYEAR una formula che mi restituisce una tabella di date, ho utilizzato CALCULATETABLE per limitare il contesto valutativo alle sole date che mi interessavano.

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

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

Calcolo qty progressive in funzione di date fatture diverse per ogni distributore

Messaggio da Enrico Galli »

Ciao :wave:
Innanzitutto faccio i miei complimenti ad Andrea90 , che a quanto pare ha letto e ben imparato i preziosissimi DAX Patterns dei nostri celebri connazionali :D
Aggiungo solo che, riguardo la formula per l'anno corrente, direi che anche la versione semplificata di Godwyn funziona bene. Ovviamente se per giorni, o mesi, non hai nessuna vendita, mostrerà le righe con dei blank, ma forse è giusto che sia così in questo caso. Concordi Andrea?

EDIT: no, mi correggo da solo: per non mostrare le date future è necessario imporre il filtro sulla MAX data di vendite. Bocciato! :lol: :oops:
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
Godwyn
Messaggi: 17 | Topic creati
Iscritto il: sab 5 dic 2020, 11:07
Luogo: Varese
Ringraziato: 4 volte

Calcolo qty progressive in funzione di date fatture diverse per ogni distributore

Messaggio da Godwyn »

Andrea90 ha scritto: lun 18 gen 2021, 17:41 Ciao Godwyn,

Qualcosa di simile (vedi file allegato).

[#TotYTD]

Codice: Seleziona tutto

=VAR DateMax =
    MAXX ( Sales; Sales[Data] )
RETURN
    CALCULATE (
        [#TotValore];
        CALCULATETABLE ( DATESYTD ( 'Calendar'[Data] ); 'Calendar'[Data] <= DateMax )
    )
[#TotPYTD]

Codice: Seleziona tutto

=VAR DateMax =
    MAXX ( Sales; Sales[Data] )
RETURN
    CALCULATE (
        [#TotYTD];
        CALCULATETABLE (
            SAMEPERIODLASTYEAR ( 'Calendar'[Data] );
            'Calendar'[Data] <= DateMax
        )
    )
A presto,

Andrea
Ciao ragazzi, torno su questa formula DAX a distanza di tempo perché mi sta facendo un po' smattare... Ho due problemi:

1) I totali non sono corretti. Se in una tabella anzichè la gerarchia data porto il prodotto o i clienti, la somma delle singole righe non fa il subtotale o il totale. Ad esempio:

Immagine

Come vedete la somma dei tre prodotti dovrebbe fare 18, invece fa 72. Non capisco quale sia il dato corretto e come risolverlo.

2) Con le anagrafiche clienti ad esempio, che sono più di 150.000, carica all'infinito e poi da errore. Troppo pesante la mole di dati.
C'è un modo per "ottimizzare la formula o "alleggerirla"? Suggerimenti?

Grazie mille...
Rispondi