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

Ausgangssituation

In der Anwendung "Adventure Works" liegen die Verkaufsdatensätze jetzt ausschließlich auf Monatsbasis vor:

Das zugrundeliegende Datenmodell folgt (wieder) 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 (diese sind ident mit den Measures in Teil 1):

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

Die erste Umsetzungsvariante ist unsere Best Practice zur Abbildung der Monatsgranularität in der Faktentabelle, nämlich der Führung einer dennoch vollständigen Datumsdimension mit allen Tagen des Jahres:

In diesem Fall können sämtliche DAX Time Intelligence Funktionen unkompliziert verwendet werden, die Berechnungsergebnisse sind immer korrekt:

Wie im Teil 1 dieser Beitragsserie gezeigt, ist die Deklaration der Datumsdimension mit der Funktion Mark as Date Table für die Berechnungen selbst in diesem Fall gar nicht relevant, aber zur Validierung dennoch empfehlenswert:

2. Monatsultimos mit Datumsformat

Die zweite Umsetzungsvariante besteht darin, die Datumsdimension auf die Monatsultimos zu reduzieren, auf die auch die Faktendatensätze zugeordnet sind:

Diese Datumsdimension kann mit der Mark as Date Table Funktion nicht deklariert werden, da die Datumstabelle lückenhaft ist (wie in Teil 1 schon gezeigt):

Die DAX Time Intelligence Berechnungen sind trotz der lückenhaften Datumsdimension großteils fehlerfrei, lediglich die DATEADD() Funktion liefert lückenhafte Werte:

Der Grund liegt im Filter Context der Berechnung: DATEADD() zeigt am 28.02.2017 den gleichen Vormonatszeitraum 01.01.2017 bis 28.01.2017, der Monatswert für Januar befindet sich aber am 31.01.2017. Daher kann im Februar kein Vormonatswert angezeigt werden. Am 31.03.2022 weil der Vergleichszeitraum 01.02.2022 bis Ende des Monats berechnet wird.

3. Monatsultimos mit Integerformat

In der dritten Umsetzungsvariante wird die Periode (= Monate) im Integerformat (= Zahl) abgebildet:

Die Beziehung zur Faktentabelle verwendet damit 2 Integerfelder. Das führt dazu, daß der ALL() bzw. REMOVEFILTER() Automatismus wie in Teil 1 beschrieben nicht aktiviert wird und die Mark as Date Table Funktion auch hier nicht verwendet werden kann:

Die DAX Time Intelligence Funktionen sind daher nicht nutzbar. Aber mit etwas Custom Code können die Berechnungen relativ einfach mit der DAX FILTER() Funktion nachgebaut werden:

Hier die Measures im Detail:

Sales YTD = 
    CALCULATE(
        [Sales];
        FILTER(
            ALL('Dim Dates');
            'Dim Dates'[Month ID] >= VALUE(Left(MAX('Dim Dates'[Month ID]);4) & "01")
            && 'Dim Dates'[Month ID] <= MAX('Dim Dates'[Month ID])
        )
    )

Sales YTD (Syntax-variante) = 
VAR varDate = MAX('Dim Dates'[Month ID])
RETURN
    CALCULATE(
        [Sales];
        'Dim Dates'[Month ID] >= VALUE(Left(varDate;4) & "01")
        && 'Dim Dates'[Month ID] <= varDate;
        ALL('Dim Dates')  // alternativ REMOVEFILTERS()
    )

Sales LM = 
IF(
    HASONEVALUE('Dim Dates'[Month ID]);
    CALCULATE(
        [Sales];
        FILTER(
            ALL('Dim Dates');
            IF(
                RIGHT(MAX('Dim Dates'[Month ID]);2) = "01";
                'Dim Dates'[Month ID] = MAX('Dim Dates'[Month ID]) - 89;
                 'Dim Dates'[Month ID] = MAX('Dim Dates'[Month ID]) - 1
            )
        )
    )
)

Fazit

Die Verwendung einer vollständigen Datumsdimension mit Datumsformat ist die einfachste und sicherste Variante, um trotz Monatsgranularität in der Faktentabelle alle Time Intelligence Funktionen nutzen zu können. Bei Verwendung von Ultimos im Datumsformat kann durch bewußte Auswahl der passenden Time Intelligence Funktionen die fehlerfreie Funktion sichergestellt werden. Bei der Abbildung der Periode im Integerformat (bspw. "201701") können mit der FILTER()-Funktion die Time Intelligence Funktionen relativ einfach nachempfunden werden, hier darf aber die Komplexität bei der Kombination mit weiteren Filtern (ggfs. in nachfolgend referenzierten Measures) nicht unterschätzt werden.