Berechnung eines Wertes in einer Pivot auf Datenbankbasis

[CleSie]

Hallo zusammen,

ich habe folgendes Problem:

Für eine Auswertung benötige ich eine Excel-Datei als Datenquelle für Diagramme, welche allerlei Informationen aus dem führenden ERP-System entnimmt und jederzeit aktuell anzeigt.
Die meisten Infos konnte ich aus dem System bereits entnehmen. Allerdings fehlt mir die Berechnung eines bestimmten Wertes, welcher nicht berücksichtig ist. Diese Berechnung sollte in die Pivot eingebunden werden.

   

Siehe Anlage 1. Die Tabelle beinhaltet die Bewegungen von Containern. Spalte C beschreibt, ob ein Behälter geholt oder gestellt wurde. Spalte E berechnet bei den Holen-Aufträgen die Standzeit beim Kunden, indem bereits im ERP-System im Auftrag das Datum der Gestellung von der Abholung subtrahiert wird. Nach dem gleichen Prinzip möchte ich die Standzeit auf dem Betriebsgelände angezeigt bekommen. Diese wird aktuell immer mit 0 angezeigt. Ziel ist also je "Stellen"-Zeile (Spalte C) mit der Bedingung der Behälternummer (Spalte B) das Datum der selben Zeile (Spalte A) minus das Datum der letzten Holen-Zeile derselben Behälternummer zu berechnen. Da in einer Pivot nicht mit Tabellenbezügen gearbeitet werden kann, bin ich gescheitert und weiß nicht weiter. Ein Programmierung im ERP-System wäre sehr aufwändig.

Falls jemand eine Idee hat, würde mir das enorm weiterhelfen.

Viele Dank im Voraus für alle Beiträge und beste Grüße
Clemens

[Gastlich]

Hallo,

die Datei hochladen, keine Bilder, Danke.

[DeltaX]

je "Stellen"-Zeile (Spalte C) mit der Bedingung der Behälternummer (Spalte B) das Datum der selben Zeile (Spalte A) minus das Datum der letzten Holen-Zeile derselben Behälternummer zu berechnen.

Anhand der Daten im Screenshot ist 0 dann ja richitg.

[CleSie]

Hallo Gastlich,

die Datei kann ich nicht hochladen, da sie wie gesagt nur im Firmennetzwerk per Datenbankabfrage funktioniert.
Ich habe jetzt einen beispielhaften Auszug dennoch mit hochgeladen. 


  Beispiel ohne Datenbank.xlsx (Größe: 13,06 KB / Downloads: 4)

Gelb ist, was ich verformelt benötige und grün habe ich händisch als SOLL eingetragen.
Aber dies ist wie gesagt nur ein Auszug der Werte. Es handelt sich weder um eine Pivot-Tabelle, noch ist diese an eine Datenbank gebunden. Tabellenbezüge funktionieren als nicht!

Viele Grüße
Clemens

---

Hi DeltaX,

im Screenshot sieht es nur korrekt aus, da überall das gleiche Datum ist.
Wenn das Datum aber überall anders wäre, käme bei Stellen trotzdem 0 raus.

Sorry für das schlechte Beispiel.

Gruß
Clemens

[DeltaX]

Lade die Daten in Powerquery, "massiere" Sie dort und lade sie dann ins Datenmodell

Code für Powerquery

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    Step1 = Table.TransformColumnTypes(Quelle,{{"datum", type date}, {"connr", Int64.Type}, {"shkz", type text}, {"contyp", type text}, {"standtg", Int64.Type}, {"kndnr", Int64.Type}, {"bez", type text}, {"strasse", type text}, {"ort", type text}, {"anzahl", Int64.Type}}),
    lastDate = Table.AddColumn(Step1, "lastDt", each if [shkz]="Stellen" then fxDataPrev([connr],[datum]) else null),
    Step2 = Table.AddColumn(lastDate, "Differenz", each if [lastDt] = null then null else Number.From([datum])-Number.From([lastDt])),
    Step3 = Table.AddColumn(Step2, "standtgNeu", each if [Differenz] = null then [standtg] else[Differenz])
in
    Step3

Und der Code für die Funktion

Code:

(connr as any, stellDt as date) =>
let
    // connr = 12560,
    // stellDt = #date(2022, 12, 22),
    Quelle = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    Step1 = Table.TransformColumnTypes(Quelle,{{"datum", type date}, {"connr", Int64.Type}, {"shkz", type text}, {"contyp", type text}, {"standtg", Int64.Type}, {"kndnr", Int64.Type}, {"bez", type text}, {"strasse", type text}, {"ort", type text}, {"anzahl", Int64.Type}}),
    #"Sortierte Zeilen" = Table.Sort(Step1,{{"datum", Order.Ascending}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Sortierte Zeilen", each ([connr] = connr)),
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Gefilterte Zeilen", each [datum] <= stellDt),
    #"Gefilterte Zeilen2" = Table.SelectRows(#"Gefilterte Zeilen1", each ([shkz] = "Holen")),
    #"Beibehaltene letzte Zeilen" = Table.LastN(#"Gefilterte Zeilen2", 1),
    datum = #"Beibehaltene letzte Zeilen"{0}[datum]
in
    datum

[CleSie]

Hallo DeltaX,

dann werde ich mich mal einlesen. Scheint vielversprechend zu sein.

Vielen Dank erstmal!