Die DAX Time Intelligence Berechnungen in Power BI benötigen einen bestimmten Aufbau der Datumsdimension, um die einwandfreie Berechnung garantieren zu können. Der Teil 1 dieser Beitragsserie zeigt anhand von ausgewählten Time Intelligence Berechnungen die Konsequenzen, sofern diese Voraussetzungen teilweise oder gar nicht erfüllt sind. Im Teil 2 wird die Abbildung der Monatsgranularität behandelt.

Ausgangssituation

In der Anwendung "Adventure Works" gibt es Verkaufsdatensätze nur an Wochentagen, an Samstagen und Sonntagen wird nicht verkauft:

Das zugrundeliegende Datenmodell folgt dem Star Schema und ist mit der Datumsdimension ("Dim Dates") mit einer 1:n Beziehung verbunden:

Sofern im folgenden nichts anderes angegeben ist, werden die folgenden Measure Berechnungen verwendet:

Sales = SUM('Fact Sales'[Sales Amount])

Year-to-Date (YTD) Berechnung:

Sales YTD = 
    CALCULATE(
        [Sales];
        DATESYTD('Dim Dates'[Date])
    )

Last Month (LM) Berechnungen (3 Varianten - mehr dazu hier):

Sales LM (DATEADD) = 
    CALCULATE(
        [Sales];
        DATEADD('Dim Dates'[Date];-1;MONTH)
    )

Sales LM (PREVIOUS) = 
    CALCULATE(
        [Sales];
        PREVIOUSMONTH('Dim Dates'[Date])
    )

Sales LM (PARALLEL-PERIOD) = 
    CALCULATE(
        [Sales];
        PARALLELPERIOD('Dim Dates'[Date];-1;MONTH)
    )

1. Vollständige Datumsdimension mit Format "Datum"

Grundsätzliche Funktionsweise

Dieser erste Anwendungsfall entspricht der Best Practice für die Datumsdimension. Die Datumsdimension beinhaltet alle Tage des Jahres - auch die unbebuchten Samstage und Sonntage::

Die Verknüpfung zur Faktentabelle erfolgt über Datumsspalten im Format "Datum":

Die Time Intelligence Berechnungen liefern die korrekten Ergebnisse sowohl auf Monatsebene ...

... als auch auf Tagesebene:

Relevanz von "Mark as Date Table"

Die Funktion Mark as Date Table sorgt grundsätzlich dafür, daß bei Verwendung einer Filterbedingung auf 'Dim Dates'[Date] automatisch ein ALL('Dim Dates') bzw. REMOVEFILTERS('Dim Dates') hinzugefügt wird, da ansonsten die Berechnung gar nicht das gewünschte Ergebnis liefern könnte (siehe dazu weiter unten):

Sales YTD = 
    CALCULATE(
        [Sales];
        DATESYTD('Dim Dates'[Date]);
        ALL('Dim Dates')
    )

Allerdings fügt die DAX Engine das ALL('Dim Dates') bereits dann automatisch hinzu, sobald die 'Dim Dates' über ein Feld vom Datentyp "Datum" in Beziehung gesetzt ist. Daher ist das Mark as Date Table für die korrekte DAX Berechnung in diesem Fall gar nicht notwendig (Quelle).

Dennoch sollte auch bei perfekter Datumsdimension die Funktion angewendet werden, da sich eventuell einschleichende Inkonsistenzen bei der Aktualisierung der Tabelle durch die Deklaration verhindert werden:

2. Vollständige Datumsdimension mit Format "Integer"

Grundsätzliche Funktionsweise

Im zweiten Anwendungsfall hat die Datumsspalte in der Datumsdimension das Format "Integer" (statt "Datum"), dieses kann mit einem Datumsfeld vom gleichen Typ in der Faktentabelle in Beziehung gesetzt werden:

Diese Datumstabelle kann - mangels Datumspalte im Datumsformat - allerdings nicht mit der Funktion Mark as Date Table deklariert werden:

Die Time Intelligence Funktionen können auch nicht mit einem Datumsfeld vom Typ "Integer" verwendet werden, der DAX Syntax wird zwar akzeptiert aber das Measure kann dann im Visual nicht berechnet werden:

Lösungsansatz A: Zusatzspalte "Date" und Mark as Date Table

Daher besteht der erste Lösungsansatz darin, die Datumstabelle mit einer Zusatzspalte "Date" im Datumsformat anzureichern:

Die Time Intelligence Funktionen können jetzt auf die Zusatzspalte "Date" referenziert werden, allerdings liefern diese - aufgrund des wirksamen Filter Context des Visuals - noch keine brauchbaren Ergebnisse (Quelle):

In diesem Fall MUSS die Datumsdimension mit der Funktion Mark as Date Table deklariert werden ...

... damit den DAX Filterfunktionen implizit ein ALL('Dim Dates') bzw. REMOVEFILTERS('Dim Dates') hinzugefügt wird und die Berechnungen die gewünschten Ergebnisse bringen:

Lösungsansatz B: ALL() oder REMOVEFILTERS() in DAX hinzufügen

Alternativ zur Zusatzspalte "Date" kann die Datumsdimension auch nur mit dem Datumsfeld vom Typ "Integer" verwendet werden. Den Time Intelligence Funktionen muß lediglich ein ALL('Dim Dates') bzw. REMOVEFILTERS('Dim Dates') hinzugefügt werden und die Berechnungen funktionieren gleichwertig:

3. Lückenhafte Datumsdimension

Im dritten Anwendungsfall wurde die Datumsdimension lückenhaft definiert. Konkret wurden die Samstage und Sonntage entfernt und dazu noch 12.01.2017 sowie der 07.02.2017, da es an diesen Tagen keine Verkäufe gibt:

Die Funktion Mark as Date Table kann in diesem Fall aufgrund der Lücken nicht mehr erfolgreich ausgeführt werden:

Die Time Intelligence Berechnungen liefern auf Monatsebene korrekte Ergebnisse auch ohne Deklaration der Datumstabelle, da die DAX Engine aufgrund der Beziehung zweier Datumsfelder den Filter Context implizit mit dem ALL('Dim Dates') bzw. REMOVEFILTERS('Dim Dates') anreichert (siehe dazu oben):

Die Berechnung auf Tagesebene ist jedoch insbesondere by der Last Month Berechnung mit DATEADD() aufgrund der fehlenden Tage äußerst lückenhaft und damit ein No-Go für eine verlässliche Reportinganwendung:

Fazit

Das Entfernen von nicht bebuchten Tagen aus der Datumstabelle ist keinesfalls eine gute Idee.

Die Datumsdimension sollte immer vollständig - also lückenlos mit allen Tagen des Jahres - angelegt werden und die Datumsspalte sollte im Datumsformat geführt werden. Die Deklaration mit der Funktion Mark as Date Table ist - wie oben gezeigt - bei solchen Datumsdimensionen nicht notwendig, schadet aber auch nicht und sollte daher weiterhin als Best Practice aktiviert werden.

Quellen:

https://www.sqlbi.com/articles/mark-as-date-table/

https://learn.microsoft.com/en-us/analysis-services/tutorial-tabular-1200/lesson-3-mark-as-date-table

https://linearis.at/blog/2019/01/22/period-over-period-berechnungen-in-power-bi-teil-1-dax-pattern-fur-yoy-qoq-mom-und-dod