Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Power Pivot Summewenns

Power Pivot Summewenns
06.10.2021 21:00:38
Gerhard
Hallo zusammen,
ich hänge bei einem Thema fest, weiß nicht weiter und bitte um eure Unterstützung.
Ich versuche gerade in Power Pivot eine Auswertung für beworbene Artikel auszuführen und würde diese gerne gegenüberstellen mit der Vorwoche. Momentan scheitere ich daran, dass Pivot erkennt, dass es einfach die Werbeartikel identifiziert und anschaut, welchen Umsatz die vor einer Woche gemacht haben.
Wenn die Datenmenge kleiner wäre würde ich wahrscheinlich einfach Summewenns machen und beim Kriterium das "Datum der Zeile minus 7 rechnen", klappt aber in Pivot nicht.
Anbei bei eine Beispielexcel dazu:
https://www.herber.de/bbs/user/148474.xlsx
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: hab PP nicht, trotzdem nachgefragt ...
07.10.2021 08:41:21
neopa
Hallo Gerhard,
... die aktuelle KW ist doch KW40 und die der Vorwoche KW39 und nicht 41 und 40 wie in Deiner Datei dargestellt.
Das von Dir angestrebte könnte man auch ohne PP mit Hilfe von PQ ermitteln. Auch PQ+P wäre möglich. Der Einfachheit halber würde ich dazu in tab_Umsatz eine Hilfsspalte anfügen, in der die KW zum Tag ermittelt wird. Wäre das eine Option für Dich?
Gruß Werner
.. , - ...
Anzeige
AW: Power Pivot Summewenns
07.10.2021 08:43:30
Yal
Hallo Gerhard,
wenn Du "Woche des Jahres" rechnest, dann hast Du einen Zahl. Danach fügst Du einen Präfix. Sieht gut aus. Aber dann ist es ein Text. Darauf kann man kein "-1" rechnen. Da verlierst Du die Information, die Du brauchst.
Füge zu m Beispiel eine Spalte "Vorwoche von": alle Einträge der KW 39 haben als "Vorwoche von" KW 40!
Mit Datum -7 bist du auf dem Holzweg.
VG
Yal
Anzeige
AW: Power Pivot Summewenns
07.10.2021 08:50:40
Luschi
Hallo Gerhard,
für Zeitraumvergleiche ist das normale Excel-Pivot nicht besonders geeignet, hier bietet 'PowerPivot für Excel' die entsprechenden 'Time-Intelligence'-DAX-Funktionen (Analysen mit Datumsbezug) an:
- Daten per PowerQuery ins Datenmodell übernehmen
- eine Kalendertabelle in Excel erstellen und ebenfalls ins Datenmodell schieben
- in PowerPivot Beziehungen zwischen der Datentabelle und der Kalendertabelle erstellen
- DAX-Mesures erstellen
- Pivottabelle daraus erstellen
Wie das geht, zeigt diese Video-Serien:
https://de.linkedin.com/learning/excel-2016-2013-pivot-berichte-mit-datenmodell-verbessern/das-add-in-power-pivot-aktivieren
Unter: 5. Time Intelligence: Analysen mit Datumsbezug ist ein Video freigeschalten: Vorjahresvergleiche mit SAMEPERIODLASTYEAR
Gruß von Luschi
aus klein-Paris
Anzeige
AW: Power Pivot Summewenns
07.10.2021 15:40:44
ChrisL
Hi
Hier mal ein Ansatz:
https://www.herber.de/bbs/user/148492.xlsx
PP Berechnete Felder:

Vorwoche =IF(AND('Zusammenführen1'[Tag]>TODAY()-14;'Zusammenführen1'[Tag]TODAY()-7;'Zusammenführen1'[Tag]
Hat natürlich nichts mit Kalenderwochen zu tun. Andererseits, wenn du mit KW rechnest hast du das Problem des Jahreswechsels und zudem kannst du meist keinen vollständigen Wochenvergleich machen. Angenommen wir denken in Arbeitswochen, dann hättest du nur am Freitagabend den vollständigen Vergleich und am Montagmorgen hat bereits die Folgewoche gestartet.
Folglich ein ganz generelles Thema, wie/was du genau berechnen möchtest.
Ich vermute die von mir vorgeschlagene Lösung ist wahrscheinlich auch nicht schneller wie ein simples SUMMEWENNS. Aus dem Bauch heraus würde ich vermuten, dass ein Filter (Daten ausserhalb vom Zeitraum ausfiltern) und Summen-Aggregation direkt in Power Query die effizienteste Lösung bei grossen Datenmengen sein könnte.
Hingegen wäre eine Power-Pivot Lösung zu wählen, wenn du gedenkst verfeinerte Filterkriterien/Datenschnitte zu verwenden z.B. nach bestimmten Artikelnummern. Mit PQ und dem skizierten Lösungsweg (Filter/Aggregation) wäre dies nicht möglich, weil du ja bereits vorgängig aggregierst und die Detailinformationen dann gar nicht mehr zur Verfügung stünden.
cu
Chris
Anzeige
AW: Power Pivot Summewenns
07.10.2021 20:42:52
Gerhard
Hi zusammen,
danke für die raschen Rückmeldungen. Sorry, ich hab mich auch schlecht ausgedrückt.
Im Grunde möchte ich immer wissen, wie hoch war der Umsatz zum Referenztag und der Referenztag wäre immer der Tag der Vorwoche.
Das wird nur in der Regel dann auf Kalenderwoche ausgewertet, arbeiten würde ich aber auf Tagebene.
Also Chris, danke. Deine Lösung ist auf jeden Fall schon mega nahe dran. Im Grunde müsste ich die Formel ja einfach umlegen können auf Tag in der Zeile -7 ?
Anzeige
AW: Power Pivot Summewenns
08.10.2021 11:09:39
ChrisL
Hi Gerhard
Ja das Kriterium für die DAX Formel wäre dann [Tag]=TODAY()-7.
Hier noch ein reines PQ-Beispiel:
https://www.herber.de/bbs/user/148505.xlsx
Wie erwähnt, ich denke es hätte Performance-Vorteile, weil die Daten vorgängig gefiltert/aggregiert werden und darum nichts unnötiges mehr ins Datenmodell geladen und berechnet werden muss. Im Gegenzug verlierst du die Detaildaten.
cu
Chris
Anzeige
AW: hierzu momentan nur festgestellt ...
08.10.2021 11:59:25
neopa
Hallo Chris,
... ich schau es mir aber später nochmal genauer an.
Deine mit PQ- ermittelte KW ist nicht die für D geltende. Es fehlt darin mE auch noch ein Abgleich mit den Artikelnummern in tab_Werbeinfo für die Ermittlung der entsprechenden Umsätze zumindest der Vorwoche. Da die Daten in Deiner Datei in Werbeinfo nicht mehr die gleichen sind, wie von Gerhard eingestellten Datei, müsste für diese für die Vorwochen ein 0-Ergebnis ergeben. Oder?
Gruß Werner
.. , - ...
Anzeige
AW: hierzu momentan nur festgestellt ...
08.10.2021 12:53:12
ChrisL
Hi Werner
Die KW habe ich gelöscht. Eigentlich hätte ich den PQ-Schritt zur Erstellung der KW löschen sollen, aber ich habe die bestehende Abfrage verwendet und "schmutzig" gelöscht.
Ich verstehe es so, dass er HEUTE mit HEUTE-7 (Freitag mit Freitag der Vorwoche) vergleichen will. Weil es in der Musterdatei keine Daten für HEUTE gab, habe ich die Quelldaten etwas verändert, so dass ein Ergebnis entsteht.
cu
Chris
Anzeige
AW: ok, wenn bezogen auf den akt. Tag, ...
08.10.2021 13:13:30
neopa
Hallo Chris,
... ich hatte die Fragestellung von Gerhard bisher auf die gesamte akt. bzw. auch Vor-Woche bezogen.
Zu der Ermittlung KW . Ich hab zur Funktion Date.WeekOfYear() kein Parameter gefunden bzw. übersehen, ob und wie man die KW für Deutschland ermittelt werden kann. Deshalb hatte ich auch vorgeschlagen in den Quelldaten die KW zu ermitteln. Oder wie müsste ich in PQ vorgehen? Kannst Du mir dazu einen Tipp geben?
Gruß Werner
.. , - ...
Anzeige
AW: ja, das meinte ich ...
08.10.2021 16:15:14
neopa
Hallo Chris,
... danke für Deinen Link.
Ich glaube aber kaum, dass ich mir das merken kann. Da werde ich wohl bei Bedarf dann doch eher auf eine entsprechende Hilfsspalte in der Quelldatenliste einfügen, bevor ich mir dass in Erinnerung rufe bzw. zurecht suche.
Gruß Werner
.. , - ...
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige

Infobox / Tutorial

Power Pivot: Summewenns und DAX-Funktionen richtig einsetzen


Schritt-für-Schritt-Anleitung

  1. Power Pivot aktivieren:

    • Für Excel 2013 oder 2016 gehe zu Datei > Optionen > Add-Ins.
    • Wähle unten COM-Add-Ins und klicke auf Gehe zu.... Aktiviere Microsoft Power Pivot für Excel und klicke auf OK.
  2. Kalendertabelle erstellen:

    • Erstelle in Excel eine Kalendertabelle, die alle relevanten Daten enthält. Achte darauf, dass ein Datumfeld vorhanden ist.
  3. Daten per Power Query importieren:

    • Gehe zu Daten > Daten abrufen > Aus anderen Quellen und importiere deine Daten.
  4. Beziehungen herstellen:

    • Öffne Power Pivot und klicke auf Beziehungen. Stelle sicher, dass deine Datentabelle mit der Kalendertabelle verknüpft ist.
  5. DAX-Messungen erstellen:

    • Nutze DAX-Funktionen, um deine Umsatzberechnungen zu erstellen. Beispielsweise kannst du eine DAX-Formel für den Umsatz der Vorwoche verwenden:
      Vorwoche = CALCULATE(SUM('Tabelle'[Umsatz]), DATEADD('Kalendertabelle'[Datum], -7, DAY))
  6. Pivot-Tabelle erstellen:

    • Erstelle eine Pivot-Tabelle und ziehe die neu erstellten DAX-Messungen in die Werte.

Häufige Fehler und Lösungen

  • Fehler: Daten werden nicht korrekt aggregiert:

    • Lösung: Überprüfe die Beziehungen zwischen den Tabellen. Stelle sicher, dass sie korrekt eingestellt sind.
  • Fehler: Die DAX-Formel gibt falsche Werte zurück:

    • Lösung: Achte darauf, dass du die richtigen Zeitintelligenz-Funktionen verwendest, wie DATEADD oder SAMEPERIODLASTYEAR.
  • Fehler: Power Pivot zeigt keine Daten an:

    • Lösung: Stelle sicher, dass die Kalendertabelle alle benötigten Daten abdeckt und dass die Datenmodellierung korrekt ist.

Alternative Methoden

  • Power Query verwenden: Anstelle von Power Pivot kannst du auch Power Query nutzen, um Daten zu aggregieren. Hierbei kannst du die Summewenn-Funktion in Power Query (PQ) verwenden, um die Umsatzdaten zu filtern und zu aggregieren.

  • VBA zur Datumsberechnung: Verwende ein VBA-Skript zur Berechnung der Vorwoche. Mit der WeekNumber-Funktion kannst du Kalendertage in Wochen umrechnen.

Function WeekNumber(DateValue As Date) As Integer
    WeekNumber = Application.WorksheetFunction.WeekNum(DateValue, 2)
End Function

Praktische Beispiele

  • Umsatzvergleich: Du kannst eine DAX-Messung erstellen, um den Umsatz der aktuellen Woche mit dem Umsatz der Vorwoche zu vergleichen:

    UmsatzVergleich = SUM('Tabelle'[Umsatz]) - [Vorwoche]
  • Power Query Beispiel für Summewenn: In Power Query kannst du eine benutzerdefinierte Spalte hinzufügen, die die Umsätze der Vorwoche ermittelt:

    = Table.AddColumn(PreviousStep, "Vorwoche Umsatz", each if [Datum] <= Date.AddDays(DateTime.LocalNow(), -7) then [Umsatz] else null)

Tipps für Profis

  • Nutze DAX-Funktionen für Zeitintelligenz: Funktionen wie SAMEPERIODLASTYEAR helfen dir, Zeitvergleiche zu vereinfachen.

  • Kalenderwoche korrekt ermitteln: Achte darauf, dass du die ISO-Woche korrekt berechnest. Hierfür kannst du den M-Code zur Berechnung der ISO-Woche in Power Query verwenden.

  • Performance optimieren: Filtere und aggregiere deine Daten bereits in Power Query, bevor sie ins Datenmodell geladen werden. Dies kann die Performance erheblich verbessern.


FAQ: Häufige Fragen

1. Wie aktiviere ich Power Query in Excel?
Du kannst Power Query aktivieren, indem du zu Datei > Optionen > Add-Ins gehst und das Add-In aktivierst.

2. Welche DAX-Funktion ist am besten für Zeitvergleiche geeignet?
Die Funktion SAMEPERIODLASTYEAR ist ideal für Jahresvergleiche. Für wöchentliche Vergleiche kannst du DATEADD verwenden.

3. Kann ich Power Query und Power Pivot zusammen verwenden?
Ja, Power Query kann verwendet werden, um Daten für Power Pivot zu transformieren und zu laden, wodurch du eine mächtige Kombination erhältst.

4. Wie berechne ich die Kalenderwoche in Power Query?
Du kannst die Funktion Date.WeekOfYear() verwenden, um die Kalenderwoche aus einem Datum zu ermitteln. Achte darauf, den richtigen Parameter für das ISO-System einzustellen.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige