Time Intelligence in Power Pivot in Excel

Van toepassing op
Excel voor Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

DAX (Data Analysis Expressions) heeft 35 functies die specifiek zijn bedoeld voor het aggregeren en vergelijken van gegevens in de loop van de tijd. In tegenstelling tot de datum- en tijdfuncties van DAX, hebben tijdintelligente functies in Excel eigenlijk geen vergelijkbaarheid. Dit komt doordat time-intelligence-functies werken met gegevens die voortdurend veranderen, afhankelijk van de context die u selecteert in draaitabellen en Power View-visualisaties.

Als u met tijdintelligente functies wilt werken, moet er een datumtabel in uw gegevensmodel zijn opgenomen. De datumtabel moet een kolom met één rij bevatten voor elke dag van elk jaar dat in uw gegevens wordt opgenomen. Deze kolom wordt beschouwd als de kolom Datum (hoewel deze elke gewenste naam kan hebben). Voor veel tijdintelligente functies is de datumkolom vereist om te kunnen berekenen op basis van de datums die u als velden in een rapport selecteert. Als u bijvoorbeeld een maateenheid hebt waarmee een saldo aan het einde van het kwartaal wordt berekend met behulp van de functie CLOSINGBALANCEQTR, moet Power Pivot naar de datumkolom in de datumtabel verwijzen om te weten wanneer het kwartaal werkelijk is begonnen. Meer informatie over datumtabellen vindt u in Datumtabellen begrijpen en maken in Power Pivot in Excel.

Functies

Functies die één datum retourneren

Functies in deze categorie retourneren één datum. Het resultaat kan vervolgens worden gebruikt als argumenten voor andere functies.

De eerste twee functies in deze categorie retourneren de eerste of laatste datum in de Date_Column in de huidige context. Dit kan handig zijn als u de eerste of laatste datum wilt vinden waarop u een transactie van een bepaald type hebt uitgevoerd. Deze functies hebben maar één argument, namelijk de naam van de datumkolom in de datumtabel.

De volgende twee functies in deze categorie zoeken de eerste of laatste datum (of ook een andere kolomwaarde) waarop een expressie een niet-lege waarde bevat. Dit wordt meestal gebruikt in situaties zoals voorraad, waarbij u de laatste voorraadhoeveelheid wilt ontvangen en u niet weet wanneer de laatste inventarisatie is gemaakt.

Zes andere functies die één datum retourneren, zijn de functies die de eerste of laatste datum van een maand, kwartaal of jaar retourneren in de huidige context van de berekening.

Functies die een tabel met datums retourneren

Er zijn zestien tijd-intelligentiefuncties die een tabel met datums retourneren. Meestal worden deze functies gebruikt als een SetFilter-argument voor de functie CALCULATE . Net als alle tijdintelligente functies in DAX heeft elke functie een datumkolom als een van de argumenten.

De eerste acht functies in deze categorie beginnen met een datumkolom in een huidige context. Als u bijvoorbeeld een meting gebruikt in een draaitabel, kan er een maand of jaar staan op de kolomlabels of rijlabels. Het netto-effect is dat de datumkolom wordt gefilterd, zodat alleen de datums voor de huidige context worden opgenomen. Uitgaande van die huidige context berekenen deze acht functies vervolgens de vorige (of volgende) dag, maand, kwartaal of jaar en retourneren deze datums in de vorm van één kolomtabel. De 'vorige' functies werken terug vanaf de eerste datum in de huidige context en de 'volgende' functies gaan vooruit vanaf de laatste datum in de huidige context.

De volgende vier functies in deze categorie zijn vergelijkbaar, maar in plaats van een vorige (of volgende) periode te berekenen, berekenen ze de set datums in de periode die 'van het begin van het jaar' is (of van het begin van het kwartaal of het jaar tot heden, of in dezelfde periode van het voorgaande jaar). Deze functies voeren alle berekeningen uit met de laatste datum in de huidige context. Met ZELFDEPERIODEVORIG jaar moet de huidige context een aaneengesloten reeks datums bevatten. Als de huidige context geen aaneengesloten reeks datums is, retourneert ZELFDEPERIODELAATSTEJAAR een fout.

De laatste vier functies in deze categorie zijn iets complexer en ook iets krachtiger. Deze functies worden gebruikt om van de set datums in de huidige context over te schakelen naar een nieuwe set datums.

  • DATEADD (Date_Column, Number_of_Intervals, interval)
  • DATUMSTUSSEN (Date_Column, Start_Date, End_Date)
  • DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, interval)

DATUMSTUSSEN berekent de set datums tussen de opgegeven begindatum en einddatum. De overige drie functies verschuiven een aantal tijdsintervallen ten opzichte van de huidige context. Het interval kan dag, maand, kwartaal of jaar zijn. Met deze functies kunt u het tijdsinterval voor een berekening eenvoudig verschuiven op een van de volgende manieren:

  • Terug twee jaar terug
  • Eén maand Terug gaan
  • Drie kwartalen vooruit gaan
  • 14 dagen Terug gaan
  • 28 dagen vooruit

U hoeft steeds alleen op te geven welk interval en hoeveel van die intervallen moeten worden verschoven. Een positief interval gaat vooruit in de tijd, een negatief interval gaat terug in de tijd. Het interval zelf wordt opgegeven met een trefwoord zoals DAG, MAAND, KWARTAAL of JAAR. Deze trefwoorden zijn geen tekenreeksen en mogen dus niet tussen aanhalingstekens worden geplaatst.

Functies die expressies over een bepaald tijdsperiode evalueren

Deze functiecategorie evalueert een expressie over een bepaalde periode. U kunt hetzelfde bereiken met CALCULATE en andere tijdintelligente functies. Voorbeeld:

= TOTALMTD (Expression, Date_Column [, SetFilter])

is precies hetzelfde als:

= CALCULATE (expressie, DATESMTD (Date_Column)[, SetFilter])

Het is echter eenvoudiger om deze tijdintelligente functies te gebruiken wanneer ze goed zijn afgestemd op het probleem dat moet worden opgelost:

  • TOTALMTD (expressie, Date_Column [, SetFilter])
  • TOTALQTD (expressie, Date_Column [, SetFilter])
  • TOTALYTD (expressie, Date_Column [, SetFilter] [,YE_Date]) *

In deze categorie vindt u ook een groep functies voor het berekenen van begin- en eindsaldo's. Er zijn bepaalde concepten die u moet begrijpen met deze specifieke functies. Ten eerste, zoals u misschien voor de hand ligt, is het beginsaldo voor elke periode hetzelfde als het eindsaldo voor de vorige periode. Het eindsaldo bevat alle gegevens tot en met het einde van de periode, terwijl het beginsaldo geen gegevens van binnen de huidige periode bevat.

Deze functies retourneren altijd de waarde van een expressie die voor een bepaald tijdstip wordt geëvalueerd. Het punt in de tijd dat voor ons belangrijk is, is altijd de laatst mogelijke datumwaarde in een kalenderperiode. De beginbalans is gebaseerd op de laatste datum van de vorige periode, terwijl de eindbalans is gebaseerd op de laatste datum van de huidige periode. De huidige periode wordt altijd bepaald door de laatste datum in de context van de huidige datum.

Aanvullende bronnen

Artikelen: Datumtabellen begrijpen en maken in Power Pivot in Excel

Naslaginformatie: Naslag voor DAX-functies op Office.com

Voorbeelden: winst- en verliesgegevensmodellering en -analyse met Microsoft PowerPivot in Excel