In (tabular) Cubes möchte man möglicherweise Berechnungen durchführen, bei denen die Anzahl der Tage zwischen zwei Datumswerten ermittelt werden sollen.
Überraschender Weise gibt es keine DateDiff-Funktion in DAX.
Zwar kursieren im Web einige Beispiele für einen Ersatz, allerdings gefallen sie mir nicht.
Hintergrundwissen
Deswegen schauen wir uns zunächst näher an, wie in (tabular) Cubes Datumswerte gespeichert werden:
Datumswerte werden intern als Zahlen gespeichert, wobei der ganzzahlige Anteil die Anzahl der Tage seit 30.12.1899 angibt und der Nachkommaanteil die Uhrzeit.
Wir können das ganz leicht im Cube sehen, indem wir ein Datum mit 1.0 (deutsch 1,0) multiplizieren (im DAX reicht *1. bzw. *1, – je nachdem welche Lokalisierung eingestellt ist ) – oder über value(…) in eine Zahl konvertieren.
Ich habe einen kleinen Cube erstellt – mit folgender Datenquelle:
select convert(datetime, ‚17.8.2011 17:53:12‘, 104) as DatumUhrzeit, convert(date, ‚17.8.2011‘, 104) as Datum
UNION ALL
select convert(datetime, ‚1.3.2000 06:13:27‘, 104), convert(date, ‚1.3.2000‘, 104)
UNION ALL
select convert(datetime, ‚5.3.2000 01:13:27‘, 104), convert(date, ‚5.3.2000‘, 104)
UNION ALL
select convert(datetime, ‚1.1.1900 5:30:00‘, 104), convert(date, ‚1.1.1900‘, 104)
UNION ALL
select convert(datetime, ‚31.12.1899 17:30:00‘, 104), convert(date, ‚31.12.1899‘, 104)
UNION ALL
select convert(datetime, ‚27.12.2014 20:22:55‘, 104), convert(date, ‚27.12.2014‘, 104)
UNION ALL
select getdate(), convert(date, getdate())
Dann habe ich jeweils eine berechnete Spalte – wie oben beschrieben – hinzugefügt. Das Ergebnis:
DateDiff von Dates
Um nun die Differenz zwischen 2 Date-Spalten zu ermitteln, muss man sie nur voneinander abziehen. Das Ergebnis hat allerdings den Datentyp DateTime, weswegen man das Ergebnis noch in ein int verwandeln muss:
([Datum2] –[Datum1]) * 1
oder
int([Datum2]-[Datum1])
Um das zu demonstrieren, habe ich meine Quelle angepasst und das Ergebnis sieht so aus:
DateDiff von DateTimes
Wenn wir aber 2 DateTimes haben, dürfen wir sie nicht einfach voneinander abziehen (also int([DatumUhrzeit2]-[DatumUhrzeit1])), da die Uhrzeiten keine rolle spielen sollen. Stattdessen müssen wir die Datumswerte zunächst nach int konvertieren (dabei rundet DAX immer ab) und dann subtrahieren:
int([DatumUhrzeit2])-int([DatumUhrzeit1])
Wir sehen, dass die falsche Methode immer dann einen zu niedrigen Wert ausweist, wenn das abgezogene Datum eine größere Uhrzeit als der Minuend aufweist.
Erweiterungen: Alter
Falls mein ein Alter bis heute berechnen will, kann man das mit der gleichen Logik machen. Die DAX-Funktionen sind:
- now() liefert das heutige Datum inkl. aktueller Uhrzeit
- today() liefert nur das heutige Datum
Diese Funktionen werden im übrigen beim Process aufgerufen und nicht beim Auswerten in einem Frontend-Programm
Erweiterungen: DateDiff in Sekunden
Ganz analog kann man natürlich vorgehen, um die Differenz in anderen Einheiten auszurechen:
Man multipliziert einfach die DateTime-Spalte mit dem entsprechenden Wert:
- * 24 für Stunden
- * 24 * 60 für Minuten
- * 24 * 60 * 60 für Sekunden
und konvertiert dann die Zahl in ein int.
Allerdings kann so eine Differenz in Monaten bzw. Jahren nicht berechnet werden. Das müsste man zunächst wohl definieren (Was bedeutet 17.1.2015 18:33 – 18.3.2012 17:55 in Monaten?) und dann ggf. über if-Statements selbst berechnen.