Einführung
Mit der kalenderbasierten Zeitintelligenz ist der Bedarf an benutzerdefinierter Zeitintelligenzlogik drastisch zurückgegangen.
Jetzt können wir benutzerdefinierte Kalender erstellen, um unsere Anforderungen an die Zeitintelligenzberechnung zu erfüllen.
Vielleicht haben Sie meinen Artikel über fortgeschrittene Zeitintelligenz gelesen:
https://towardsdatascience.com/advanced-time-intelligence-in-dax-with-performance-in-mind/
Der Großteil der benutzerdefinierten Logik wird nicht mehr benötigt.
Aber es gibt immer noch Szenarien, in denen wir benutzerdefinierte Berechnungen benötigen, wie zum Beispiel den laufenden Durchschnitt.
Vor einiger Zeit SQLBI hat einen Artikel geschrieben über die Berechnung des laufenden Durchschnitts.
Dieses Stück verwendet dieselben dort beschriebenen Prinzipien in einem etwas anderen Ansatz.
Sehen wir uns an, wie wir mit den neuen Kalendern den laufenden Durchschnitt über drei Monate berechnen können.
Mit klassischer Zeitintelligenz
Zunächst verwenden wir den Commonplace-Gregorianischen Kalender mit der klassischen Time Intelligence-Datumstabelle.
Ich verwende einen ähnlichen Ansatz wie im SQLBI-Artikel beschrieben, der im Abschnitt „Referenzen“ weiter unten verlinkt ist.
Operating Common by Month =
// 1. Get the primary and final Date for the present Filter Context
VAR MaxDate = MAX( 'Date'(Date) )
// 2. Generate the Date vary wanted for the Shifting common (three months)
VAR DateRange =
DATESINPERIOD( 'Date'(Date)
,MaxDate
,-3
,MONTH
)
// 3. Generate a desk filtered by the Date Vary generated at step 2
// This desk incorporates solely three rows
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Date'(MonthKey)
, "#Gross sales", (Sum On-line Gross sales)
)
,DateRange
)
RETURN
// 4. Calculate the Common over the three values within the desk generate in step 3
AVERAGEX(SalesByMonth, (#Gross sales))
Wenn ich diese Maßnahme in DAX Studio ausführe, erhalte ich die erwarteten Ergebnisse:
So weit, ist es intestine.
Verwendung eines Standardkalenders
Als nächstes habe ich einen Kalender mit dem Namen „Gregorianischer Kalender“ erstellt und den Code geändert, um diesen Kalender zu verwenden.
Um das Verständnis zu erleichtern, habe ich die Datumstabelle in eine neue Tabelle mit dem Namen „Gregorian Date Desk“ kopiert.
Die Änderung erfolgt beim Aufruf des DATESINPERIOD() Funktion.
Anstatt die Datumsspalte zu verwenden, verwende ich den neu erstellten Kalender:
Operating Common by Month =
// 1. Get the primary and final Date for the present Filter Context
VAR MaxDate = MAX( 'Gregorian Date Desk'(Date) )
// 2. Generate the Date vary wanted for the Shifting common (three months)
VAR DateRange =
DATESINPERIOD( 'Gregorian Calendar'
,MaxDate
,-3
,MONTH
)
// 3. Generate a desk filtered by the Date Vary generated at step 2
// This desk incorporates solely three rows
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Gregorian Date Desk'(MonthKey)
, "#Gross sales", (Sum On-line Gross sales)
)
,DateRange
)
RETURN
// 4. Calculate the Common over the three values within the desk generate in step 3
AVERAGEX(SalesByMonth, (#Gross sales))
Wie erwartet sind die Ergebnisse identisch:
Die Leistung ist ausgezeichnet, da diese Abfrage in 150 Millisekunden abgeschlossen ist.
Verwenden eines benutzerdefinierten Kalenders
Aber was passiert, wenn Sie einen benutzerdefinierten Kalender verwenden?
Zum Beispiel ein Kalender mit 15 Monaten professional Jahr und 31 Tagen für jeden Monat?
Einen solchen Kalender habe ich für meinen Artikel erstellt, der Anwendungsfälle für kalenderbasierte Zeitintelligenz beschreibt (siehe Hyperlink oben und im Abschnitt Referenzen).
Wenn Sie sich den Code für die Kennzahl ansehen, werden Sie feststellen, dass er anders ist:
Operating Common by Month (Customized) =
VAR LastSelDate = MAX('Monetary Calendar'(CalendarEndOfMonthDate))
VAR MaxDateID = CALCULATE(MAX('Monetary Calendar'(ID_Date))
,REMOVEFILTERS('Monetary Calendar')
,'Monetary Calendar'(CalendarEndOfMonthDate) = LastSelDate
)
VAR MinDateID = CALCULATE(MIN('Monetary Calendar'(ID_Date))
,REMOVEFILTERS('Monetary Calendar')
,'Monetary Calendar'(CalendarEndOfMonthDate) = EOMONTH(LastSelDate, -2)
)
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Monetary Calendar'(CalendarYearMonth)
, "#Gross sales", (Sum On-line Gross sales)
)
,'Monetary Calendar'(ID_Date) >= MinDateID
&& 'Monetary Calendar'(ID_Date) <= MaxDateID
)
RETURN
AVERAGEX(SalesByMonth, (#Gross sales))Der Grund für die Änderungen liegt darin, dass dieser Tabelle eine Datumsspalte fehlt, die mit verwendet werden kann DATESINPERIOD() Funktion. Aus diesem Grund muss ich benutzerdefinierten Code verwenden, um den Wertebereich für zu berechnen ID_Date.
Das sind die Ergebnisse:
Wie Sie überprüfen können, sind die Ergebnisse korrekt.
Optimierung durch Verwendung eines Tagesindex
Aber wenn ich die Leistung analysiere, ist sie nicht so toll.
Die Berechnung der Ergebnisse dauert quick eine halbe Sekunde.
Wir können die Leistung verbessern, indem wir die Notwendigkeit beseitigen, das Minimal und das Most abzurufen ID_Date und eine effizientere Berechnung durchzuführen.
Ich weiß, dass jeder Monat 31 Tage hat.
Um drei Monate zurückzugehen, weiß ich, dass ich 93 Tage zurückgehen muss.
Damit kann ich eine schnellere Model der Maßnahme erstellen:
Operating Common by Month (Monetary) =
// Step 1: Get the final Month (ID)
VAR SelMonth = MAX('Monetary Calendar'(ID_Month))
// Step 2: Generate the Date Vary from the final 93 days
VAR DateRange =
TOPN(93
,CALCULATETABLE(
SUMMARIZECOLUMNS('Monetary Calendar'(ID_Date))
,REMOVEFILTERS('Monetary Calendar')
,'Monetary Calendar'(ID_Month) <= SelMonth
)
,'Monetary Calendar'(ID_Date), DESC
)
// 3. Generate a desk filtered by the Date Vary generated at step 2
// This desk incorporates solely three rows
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Monetary Calendar'(ID_Month)
, "#Gross sales", (Sum On-line Gross sales)
)
,DateRange
)
RETURN
// 4. Calculate the Common over the three values within the desk generate in step 3
AVERAGEX(SalesByMonth, (#Gross sales))
Dieses Mal habe ich das verwendet TOPN() Funktion zum Abrufen der 93 vorherigen Zeilen aus der Tabelle „Finanzkalender“ und verwendete diese Liste als Filter.
Die Ergebnisse sind identisch mit der Vorgängerversion:
Diese Model benötigt nur 118 ms.
Aber können wir mit der Optimierung noch weiter gehen?
Als nächstes habe ich dem Finanzkalender eine neue Spalte hinzugefügt, um den Zeilen Ränge zuzuweisen. Nun hat jedes Datum eine eindeutige Nummer, die in direktem Zusammenhang mit der Reihenfolge steht:
Die Kennzahl, die diese Spalte verwendet, ist die folgende:
Operating Common by Month (Monetary) =
// Step 1: Get the final Month (ID)
VAR MaxDateRank = MAX('Monetary Calendar'(ID_Date_RowRank))
// Step 2: Generate the Date Vary from the final 93 days
VAR DateRange =
CALCULATETABLE(
SUMMARIZECOLUMNS('Monetary Calendar'(ID_Date))
,REMOVEFILTERS('Monetary Calendar')
,'Monetary Calendar'(ID_Date_RowRank) <= MaxDateRank
&& 'Monetary Calendar'(ID_Date_RowRank) >= MaxDateRank - 92
)
--ORDER BY 'Monetary Calendar'(ID_Date) DESC
// 3. Generate a desk filtered by the Date Vary generated at step 2
// This desk incorporates solely three rows
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Monetary Calendar'(ID_Month)
, "#Gross sales", (Sum On-line Gross sales)
)
,DateRange
)
RETURN
// 4. Calculate the Common over the three values within the desk generate in step 3
AVERAGEX(SalesByMonth, (#Gross sales))
Das Ergebnis ist das gleiche, ich zeige es nicht noch einmal.
Aber hier ist der Vergleich aus der Ausführungsstatistik:
Wie Sie sehen können, wird die Model verwendet TOPN() ist etwas langsamer als derjenige, der die RowRank-Spalte verwendet.
Aber die Unterschiede sind marginal.
Noch wichtiger ist, dass die Model, die die RowRank-Spalte verwendet, mehr Daten benötigt, um die Berechnungen abzuschließen. Weitere Informationen finden Sie in der Spalte „Zeilen“.
Dies bedeutet mehr RAM-Auslastung.
Aber bei dieser geringen Anzahl an Zeilen sind die Unterschiede immer noch marginal.
Sie haben die Wahl, welche Model Sie bevorzugen.
Verwendung eines Wochenkalenders
Schauen wir uns abschließend noch eine wöchentliche Berechnung an.
Dieses Mal möchte ich den gleitenden Durchschnitt der letzten drei Wochen berechnen.
Da die kalenderbasierte Zeitintelligenz die Erstellung eines wochenbasierten Kalenders ermöglicht, ist die Messung der zweiten sehr ähnlich:
Operating Common by Week =
// 1. Get the primary and final Date for the present Filter Context
VAR MaxDate = MAX( 'Gregorian Date Desk'(Date) )
// 2. Generate the Date vary wanted for the Shifting common (three months)
VAR DateRange =
DATESINPERIOD( 'Week Calendar'
,MaxDate
,-3
,WEEK
)
// 3. Generate a desk filtered by the Date Vary generated at step 2
// This desk incorporates solely three rows
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Gregorian Date Desk'(WeekKey)
, "#Gross sales", (Sum On-line Gross sales)
)
,DateRange
)
RETURN
// 4. Calculate the Common over the three values within the desk generate in step 3
AVERAGEX(SalesByMonth, (#Gross sales))
Der entscheidende Teil ist, dass ich den Parameter „WEEK“ verwende DATESINPERIOD() Anruf.
Das ist alles.
Dies ist das Ergebnis der Abfrage:
Die Leistung ist ausgezeichnet, mit Ausführungszeiten unter 100 ms.
Beachten Sie, dass wöchentliche Berechnungen nur mit der kalenderbasierten Zeitintelligenz möglich sind.
Abschluss
Wie Sie gesehen haben, macht die kalenderbasierte Zeitintelligenz das Leben mit benutzerdefinierter Logik einfacher: Wir müssen nur den Kalender anstelle einer Datumsspalte an die Funktionen übergeben. Und wir können wöchentliche Intervalle berechnen.
Der aktuelle Funktionsumfang umfasst jedoch kein Semesterintervall. Wenn wir semesterbasierte Ergebnisse berechnen müssen, müssen wir entweder die klassische Zeitintelligenz verwenden oder benutzerdefinierten Code schreiben.
Wir benötigen jedoch weiterhin eine benutzerdefinierte Logik, insbesondere wenn unsere Kalendertabelle keine Datumsspalte enthält. In solchen Fällen können wir die Standardfunktionen der Zeitintelligenz nicht verwenden, da sie immer noch mit Datumsspalten arbeiten.
Denken Sie daran: Die wichtigste Aufgabe bei der Arbeit mit kalenderbasierter Zeitintelligenz ist die Erstellung einer konsistenten und vollständigen Kalendertabelle. Aus meiner Erfahrung ist dies die komplexeste Aufgabe.
Nebenbei bemerkt habe ich auf daxlib.org einige interessante Funktionen zum Thema „laufender Durchschnitt“ gefunden.
Ich habe im Abschnitt „Referenzen“ unten einen Hyperlink zu den Funktionen hinzugefügt.
Diese Funktionen folgen einem völlig anderen Muster, ich wollte sie jedoch einbeziehen, um ein vollständiges Bild dieses Themas zu erstellen.
Referenzen
Der erwähnte SQLBI.com-Artikel zur Berechnung des laufenden Durchschnitts:
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax
Time Collection funktioniert auf daxlib.org mit einem anderen Ansatz:
https://daxlib.org/package deal/TimeSeries.MovingAverage
Hier ist mein letzter Artikel, in dem ich kalenderbasierte Zeitintelligenz erkläre:
https://towardsdatascience.com/use-cases-for-the-new-calendar-based-time-intelligence/
Wie in meinen vorherigen Artikeln verwende ich den Contoso-Beispieldatensatz. Sie können den ContosoRetailDW-Datensatz kostenlos von Microsoft herunterladen Hier.
Die Contoso-Daten können wie beschrieben unter der MIT-Lizenz frei verwendet werden in diesem Dokument. Ich habe den Datensatz geändert, um die Daten auf aktuelle Daten zu verschieben.