Datumtabellen in Power Pivot zijn essentieel om te bladeren en gegevens over een bepaalde periode te berekenen. In dit artikel vindt u uitgebreide informatie over datumtabellen en hoe u deze kunt maken in Power Pivot. In dit artikel wordt met name het volgende beschreven:
- Een aantal redenen waarom een datumtabel belangrijk is voor het bladeren en berekenen van gegevens op datum en tijd.
- Power Pivot gebruiken om een datumtabel toe te voegen aan het gegevensmodel.
- Nieuwe datumkolommen maken, zoals Jaar, Maand en Periode in een datumtabel.
- Relaties maken tussen datumtabellen en feitentabellen.
- Werken met de tijd.
Dit artikel is bedoeld voor nieuwe gebruikers van Power Pivot. Het is echter belangrijk om al een goed begrip te hebben van het importeren van gegevens, het maken van relaties en het maken van berekende kolommen en metingen.
In dit artikel wordt niet beschreven hoe u DAX-Time-Intelligence functies kunt gebruiken in maatformules. Zie Time Intelligence in Power Pivot in Excel voor meer informatie over het maken van metingen met DAX Time Intelligence-functies.
Opmerking
In Power Pivot zijn de namen 'meting' en 'berekend veld' synoniem. We gebruiken de naam meting in dit artikel. Zie Metingen in Power Pivot voor meer informatie.
Inhoud
Datumtabellen
Bij bijna alle gegevensanalyse kunt u gegevens bekijken en vergelijken over een bepaalde datum. U wilt bijvoorbeeld de verkoopbedragen van het afgelopen fiscale kwartaal optellen en deze totalen vervolgens vergelijken met andere kwartalen, of u wilt een eindsaldo aan het einde van de maand berekenen voor een rekening. In elk van deze gevallen gebruikt u datums als een manier om verkooptransacties of saldi voor een bepaalde periode te groeperen en samen te voegen.
Power View-rapport
Een datumtabel kan veel verschillende voorstellingen van datums en tijden bevatten. Een datumtabel bevat bijvoorbeeld vaak kolommen zoals Fiscaal jaar, Maand, Kwartaal of Periode, die u als velden in een lijst met velden kunt selecteren wanneer u uw gegevens in draaitabellen of Power View-rapporten segmenteert en filtert.
Lijst met Power View-velden
Als u wilt dat datumkolommen zoals Jaar, Maand en Kwartaal alle datums binnen hun respectieve bereik bevatten, moet de datumtabel ten minste één kolom met een aaneengesloten set datums bevatten. Dat wil zeggen dat deze kolom een rij moet hebben voor elke dag van elk jaar dat is opgenomen in de datumtabel.
Als de gegevens waarin u wilt bladeren bijvoorbeeld datums hebben van 1 februari 2010 tot en met 30 november 2012 en u rapporteert over een kalenderjaar, hebt u behoefte aan een datumtabel met ten minste een datumbereik van 1 januari 2010 tot en met 31 december 2012. Elk jaar in de datumtabel moet alle dagen voor elk jaar bevatten. Als u uw gegevens regelmatig moet vernieuwen met nieuwere gegevens, kunt u de einddatum met een jaar of twee uitstellen, zodat u de datumtabel niet telkens hoeft bij te werken.
Datumtabel met een aaneengesloten set datums
Als u rapporteert over een fiscaal jaar, kunt u een datumtabel maken met een aaneengesloten reeks datums voor elk fiscaal jaar. Als het fiscale jaar bijvoorbeeld op 1 maart begint en u gegevens hebt voor het fiscale jaar 2010 tot en met de huidige datum (bijvoorbeeld boekjaar 2013), kunt u een datumtabel maken die begint op 1-3-2009 en die ten minste elke dag van elk boekjaar tot en met de laatste datum in het fiscale jaar 2013 bevat.
Als u zowel voor het kalenderjaar als voor het fiscale jaar wilt rapporteren, hoeft u geen afzonderlijke datumtabellen te maken. Eén datumtabel kan kolommen bevatten voor een kalenderjaar, een fiscaal jaar en zelfs een kalender met een periode van dertien perioden van vier weken. Het belangrijkste is dat uw datumtabel een aaneengesloten reeks datums bevat voor alle opgenomen jaren.
Een datumtabel toevoegen aan het gegevensmodel
U kunt een datumtabel op verschillende manieren toevoegen aan uw gegevensmodel:
- Importeren uit een relationele database of een andere gegevensbron.
- Maak een datumtabel in Excel en kopieer de datum naar een nieuwe tabel in PowerPivot of maak een koppeling hiernaar.
- Importeren uit Microsoft Azure Marketplace.
Laten we elk van deze nader bekijken.
Importeren uit een relationele database
Als u sommige of alle gegevens importeert uit een datawarehouse of een ander type relationele database, bestaat er waarschijnlijk al een datumtabel en relaties tussen de tabel en de rest van de gegevens die u importeert. De datums en notatie komen waarschijnlijk overeen met de datums in uw feitengegevens, en de datums beginnen waarschijnlijk ver in het verleden en gaan ver in de toekomst door. De datumtabel die u wilt importeren kan erg groot zijn en een datumbereik bevatten dat verder gaat dan wat u in uw gegevensmodel moet opnemen. U kunt de geavanceerde filterfuncties van de wizard Tabel importeren van Power Pivot gebruiken om selectief alleen de datums en kolommen te kiezen die u echt nodig hebt. Hierdoor kan de werkmap aanzienlijk kleiner worden en de prestaties verbeteren.
Wizard Tabel importeren
In de meeste gevallen hoeft u geen extra kolommen te maken, zoals Fiscaal jaar, Week, Maandnaam, enzovoort, omdat deze al in de geïmporteerde tabel aanwezig zijn. In sommige gevallen moet u, nadat u de datumtabel in uw gegevensmodel hebt geïmporteerd, mogelijk extra datumkolommen maken, afhankelijk van een specifieke rapportagebehoefte. Gelukkig is dit eenvoudig te doen met DAX. Het maken van datumtabelvelden wordt later besproken. Elke omgeving is anders. Als u niet zeker weet of uw gegevensbronnen een gerelateerde datum- of kalendertabel hebben, neemt u contact op met de databasebeheerder.
Een datumtabel maken in Excel
U kunt een datumtabel maken in Excel en deze vervolgens kopiëren naar een nieuwe tabel in het gegevensmodel. Dit is echt heel eenvoudig te doen en het geeft je veel flexibiliteit.
Wanneer u een datumtabel maakt in Excel, begint u met één kolom met een aaneengesloten datumbereik. U kunt vervolgens extra kolommen maken, zoals Jaar, Kwartaal, Maand, Fiscaal jaar, Periode, enzovoort. in het Excel-werkblad met behulp van Excel-formules, of nadat u de tabel in het gegevensmodel hebt gekopieerd, kunt u deze maken als berekende kolommen. Het maken van extra datumkolommen in Power Pivot wordt beschreven in de sectie Nieuwe datumkolommen toevoegen aan de datumtabel verderop in dit artikel.
Procedure: Maak een datumtabel in Excel en kopieer deze naar het gegevensmodel
Typ in Excel in een leeg werkblad in cel A1 de naam van een kolomkop om een datumbereik aan te geven. Meestal is dit zoiets als Datum, Datum/tijd of Datumsleutel.
Typ een begindatum in cel A2. Bijvoorbeeld 1-1-2010.
Klik op de vulgreep en sleep deze omlaag naar het nummer van de rij met een einddatum. Bijvoorbeeld 31-12-2016.
Selecteer alle rijen in de kolom Datum (inclusief de koptekst in cel A1).
Klik in de groep Stijlen op Opmaken als tabel en selecteer een stijl.
Klik in het dialoogvenster Opmaken als tabel op OK.
Kopieer alle rijen, inclusief de koptekst.
Klik in Power Pivot op het tabblad Start op Plakken.
Typ een naam bij Voorbeeld van plakvoorbeeld>Tabelnaam, zoals Date of Calendar. Laat het selectievakje Eerste rij als kolomkoppen gebruikeningeschakeld en klik op OK.
De nieuwe datumtabel (in dit voorbeeld Calendar genoemd) in PowerPivot ziet er als volgt uit:
Opmerking
U kunt ook een gekoppelde tabel maken met behulp van Toevoegen aan gegevensmodel. Hierdoor wordt de werkmap echter onnodig groot omdat de werkmap twee versies van de datumtabel bevat. één in Excel en één in Power Pivot.
Opmerking
De naamdatum is een sleutelwoord in Power Pivot. Als u de tabel die u in Power Pivot maakt een naam geeft, moet u de tabelnaam tussen enkele aanhalingstekens plaatsen in alle DAX-formules waarin naar de tabel wordt verwezen in een argument. Alle voorbeeldafbeeldingen en -formules in dit artikel verwijzen naar een datumtabel met de naam Calendar die in Power Pivot is gemaakt.
U hebt nu een datumtabel in het gegevensmodel. Met DAX kunt u nieuwe datumkolommen toevoegen, zoals Jaar, Maand, enzovoort.
Nieuwe datumkolommen toevoegen aan de datumtabel
Een datumtabel met één datumkolom met één rij voor elke dag voor elk jaar is belangrijk voor het definiëren van alle datums in een datumbereik. Het is ook nodig om een relatie te maken tussen de feitentabel en de datumtabel. Maar die ene datumkolom met één rij voor elke dag is niet handig bij het analyseren op datum in een draaitabel of Power View-rapport. U wilt dat de datumtabel kolommen bevat waarmee u uw gegevens voor een bereik of een groep datums kunt aggregeren. U wilt bijvoorbeeld verkoopbedragen optellen per maand of kwartaal, of u kunt een meting maken waarmee de groei op jaarbasis wordt berekend. In elk van deze gevallen heeft uw datumtabel jaar-, maand- of kwartaalkolommen nodig waarmee u de gegevens voor die periode kunt aggregeren.
Als u de datumtabel uit een relationele gegevensbron hebt geïmporteerd, bevat deze mogelijk al de verschillende typen datumkolommen die u wilt gebruiken. In sommige gevallen wilt u mogelijk enkele van deze kolommen aanpassen of extra datumkolommen maken. Dit geldt vooral als u uw eigen datumtabel in Excel maakt en deze kopieert naar het gegevensmodel. Gelukkig kunt u eenvoudig nieuwe datumkolommen maken in Power Pivot met de datum- en tijdfuncties in DAX.
Tip
Als u nog niet met DAX hebt gewerkt, kunt u het beste beginnen met het leren met de snelstartgids: leer de grondbeginselen van DAX in 30 minuten op Office.com.
Datum- en tijdfuncties in DAX
Als u ooit hebt gewerkt met datum- en tijdfuncties in Excel-formules, bent u waarschijnlijk bekend met de datum- en tijdfuncties. Hoewel deze functies vergelijkbaar zijn met hun tegenhangers in Excel, zijn er enkele belangrijke verschillen:
- Datum- en tijdfuncties in DAX gebruiken een datetime-gegevenstype.
- Ze kunnen waarden uit een kolom als argument gebruiken.
- Ze kunnen worden gebruikt om datumwaarden als resultaat te geven en/of te bewerken.
Deze functies worden vaak gebruikt bij het maken van aangepaste datumkolommen in een datumtabel, dus het is belangrijk dat u de functies begrijpt. We gebruiken een aantal van deze functies om kolommen te maken voor Jaar, Kwartaal, Fiscale Maand, enzovoort.
Opmerking
Datum- en tijdfuncties in DAX zijn niet hetzelfde als tijdintelligente functies. Meer informatie over Time Intelligence in Power Pivot in Excel.
DAX bevat de volgende datum- en tijdfuncties:
- DATUM
- DATUMWAARDE
- VOLGENDE DAG
- ZELFDE.DAG
- LAATSTE.DAG
- HOUR
- MINUTE
- MONTH
- NU
- SECOND
- TIJD
- TIJDWAARDE
- VANDAAG
- WEEKDAG
- WEEKNUM
- YEAR
- JAAR.DEEL
Er zijn nog veel meer DAX-functies die u in uw formules kunt gebruiken. Veel van de hier beschreven formules maken bijvoorbeeld gebruik van wiskundige en trigonometrische functies zoals MOD en TRUNC, logische functies zoals ALS en tekstfuncties zoals FORMAT Zie de sectie Overige informatiebronnen verderop in dit artikel voor meer informatie over andere DAX-functies.
Voorbeelden van formules voor een kalenderjaar
In de volgende voorbeelden worden formules beschreven die worden gebruikt om extra kolommen te maken in de datumtabel genaamd Calendar. Eén kolom, Datum genaamd, bestaat al en bevat een aaneengesloten bereik van datums van 1-1-2010 tot en met 31-12-2016.
Jaar
=JAAR([datum])
In deze formule retourneert de functie JAAR het jaar uit de waarde in de kolom Datum. Omdat de waarde in de kolom Date van het gegevenstype datetime is, weet de functie YEAR hoe het jaar daaruit moet worden opgehaald.
maand
=MAAND([datum])
In deze formule kunnen we, net als bij de functie JAAR, eenvoudig de functie MAAND gebruiken om een maandwaarde uit de kolom Datum te retourneren.
Kwartaal
=INTEGER(([Maand]+2)/3)
In deze formule gebruiken we de functie INTEGER om een datumwaarde als een geheel getal te retourneren. Het argument dat we opgeven voor de functie INTEGER is de waarde uit de kolom Maand, voeg dan 2 toe en deel dat door 3 om ons kwartaal te krijgen, 1 tot en met 4.
Maandnaam
=FORMAT([datum];"mmmm")
In deze formule gebruiken we voor het ophalen van de maandnaam de functie NOTATIE om een numerieke waarde uit de kolom Date te converteren naar tekst. We geven de kolom Datum op als het eerste argument en vervolgens de opmaak; We willen dat alle tekens worden weergegeven voor onze maandnaam, dus gebruiken we "mmmm". Dit levert het volgende resultaat op:
Als we de maandnaam, afgekort tot drie letters, als resultaat willen geven, gebruiken we "mmm" in het argument notatie.
Dag van de week
=FORMAT([datum],"ddd")
In deze formule gebruiken we de functie NOTATIE om de naam van de dag op te halen. Omdat we alleen een verkorte dagnaam willen, geven we 'ddd' op in het argument notatie.
Voorbeelddraaitabel
Wanneer u velden hebt voor datums, zoals Jaar, Kwartaal, Maand enzovoort, kunt u deze gebruiken in een draaitabel of rapport. In de volgende afbeelding ziet u bijvoorbeeld het veld SalesAmount uit de feitentabel Sales in VALUES, en Year and Quarter uit de dimensietabel Calendar in ROWS. SalesAmount wordt samengevoegd voor de context van het jaar en het kwartaal.
Voorbeelden van formules voor een fiscaal jaar
Fiscaal jaar
=ALS([Maand]<= 6;[Jaar];[Jaar]+1)
In dit voorbeeld begint het fiscale jaar op 1 juli.
Er is geen functie beschikbaar waarmee u een fiscaal jaar kunt extraheren uit een datumwaarde, omdat de begin- en einddatum van een fiscaal jaar vaak afwijken van die van een kalenderjaar. Om het fiscale jaar op te halen, gebruiken we eerst een ALS-functie om te testen of de waarde voor Maand kleiner dan of gelijk is aan 6. Als de waarde voor Maand kleiner dan of gelijk is aan 6, wordt in het tweede argument de waarde uit de kolom Jaar geretourneerd. Als dit niet het geval is, wordt de waarde van Year geretourneerd en wordt 1 opgeteld.
Een andere manier om een waarde aan het einde van de maand van het fiscale jaar op te geven, is door een meting te maken die alleen de maand opgeeft. Voorbeeld: FYE:=6. U kunt dan naar de naam van de meting verwijzen in plaats van het maandnummer. Bijvoorbeeld =ALS([Maand]<=[IJ];[Jaar];[Jaar]+1). Dit biedt meer flexibiliteit bij het verwijzen naar de laatste maand van het fiscale jaar in verschillende formules.
Fiscal Month
=ALS([Maand]<= 6; 6+[Maand]; [Maand]- 6)
In deze formule geven we op als de waarde voor [Maand] kleiner is dan of gelijk is aan 6, nemen we 6 en tellen we de waarde van Maand bij elkaar op. Anders wordt 6 afgetrokken van de waarde van [Maand].
Fiscaal kwartaal
=INTEGER(([FiscaalMaand]+2)/3)
De formule die we gebruiken voor FiscalQuarter is vrijwel hetzelfde als voor Quarter in ons kalenderjaar. Het enige verschil is dat we [FiscalMonth] opgeven in plaats van [Month].
Feestdagen of speciale data
Het is misschien handig om een datumkolom op te nemen waarin wordt aangegeven dat bepaalde datums feestdagen zijn of een andere speciale datum. U kunt bijvoorbeeld de verkooptotalen voor nieuwjaarsdag optellen door een veld Feestdagen toe te voegen aan een draaitabel, als een slicer of filter. In andere gevallen kunt u deze datums uitsluiten van andere datumkolommen of in een meting.
Het opnemen van feestdagen of speciale dagen is vrij eenvoudig. U kunt in Excel een tabel maken met de datums die u wilt opnemen. U kunt het gegevensmodel vervolgens kopiëren of toevoegen als gekoppelde tabel of toevoegen aan het gegevensmodel. In de meeste gevallen is het niet nodig om een relatie te maken tussen de tabel en de tabel Calendar. Alle formules waarnaar wordt verwezen, kunnen de functie LOOKUPVALUE gebruiken om waarden op te halen.
Hieronder ziet u een voorbeeld van een tabel die is gemaakt in Excel en die feestdagen bevat die moeten worden toegevoegd aan de datumtabel:
| Datum | Feestdag |
|---|---|
| 1/1/2010 | Nieuwjaar |
| 11/25/2010 | Thanksgiving |
| 12/25/2010 | Kerstmis |
| 01-01-2011 | Nieuwjaar |
| 11/24/2011 | Thanksgiving |
| 12/25/2011 | Kerstmis |
| 1-1-2012 | Nieuwjaar |
| 22.11.12 | Thanksgiving |
| 12/25/2012 | Kerstmis |
| 1/1/2013 | Nieuwjaar |
| 11/28/2013 | Thanksgiving |
| 12/25/2013 | Kerstmis |
| 11/27/2014 | Thanksgiving |
| 12/25/2014 | Kerstmis |
| 1-1-2014 | Nieuwjaar |
| 11/27/2014 | Thanksgiving |
| 12/25/2014 | Kerstmis |
| 1/1/2015 | Nieuwjaar |
| 11/26/2014 | Thanksgiving |
| 12/25/2015 | Kerstmis |
| 01.01.16 | Nieuwjaar |
| 11/24/2016 | Thanksgiving |
| 12/25/2016 | Kerstmis |
In de datumtabel maken we een kolom met de naam Holiday en gebruiken we een formule zoals deze:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Laten we deze formule eens nader bekijken.
We gebruiken de functie LOOKUPVALUE om waarden op te halen uit de kolom Holiday in de tabel Holidays. In het eerste argument geven we de kolom op waarin de resultaatwaarde zal staan. We specificeren de kolom Feestdagen in de tabel Feestdagen , omdat dit de waarde is die we als resultaat willen hebben.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Vervolgens geven we het tweede argument op, de zoekkolom met de datums waarnaar we willen zoeken. We geven de kolom Datum in de tabel Feestdagen als volgt op:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Ten slotte geven we de kolom in de tabel Calendar op met de datums die we zoeken in de tabel Feestdagen. Dit is natuurlijk de kolom Date in de tabel Calendar.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
De kolom Feestdagen retourneert de feestdagnaam voor elke rij met een datumwaarde die overeenkomt met een datum in de tabel Feestdagen.
Aangepaste agenda - perioden van dertien vier weken
Sommige organisaties, zoals retail of foodservice, rapporteren vaak over verschillende periodes, zoals dertien periodes van vier weken. Bij een kalender met een periode van dertien vier weken is elke periode 28 dagen; daarom bevat elke periode vier maandagen, vier dinsdagen, vier woensdagen, enzovoort. Elke periode bevat hetzelfde aantal dagen en vakantiedagen vallen elk jaar binnen dezelfde periode. Je kunt ervoor kiezen om een periode op elke dag van de week te laten beginnen. Net als bij datums in een kalender of fiscaal jaar kunt u met DAX extra kolommen met aangepaste datums maken.
In de onderstaande voorbeelden begint de eerste volledige periode op de eerste zondag van het fiscale jaar. In dit geval begint het boekjaar op 1 september.
Week
Deze waarde geeft ons het weeknummer vanaf de eerste volledige week van het fiscale jaar. In dit voorbeeld begint de eerste volledige week op zondag, dus de eerste volledige week van het eerste fiscale jaar in de tabel Calendar begint in feite op 4-7-2010 en loopt door tot en met de laatste volledige week in de tabel Calendar. Hoewel deze waarde zelf niet zo handig is bij analyses, moet u toch berekeningen uitvoeren voor gebruik in andere formules met perioden van 28 dagen.
=INT([datum]-40356)/7)
Laten we deze formule eens nader bekijken.
We maken eerst een formule die waarden uit de kolom Date als geheel getal retourneert, zoals hieronder:
=INTEGER([datum])
We willen dan op zoek gaan naar de eerste zondag van het eerste fiscale jaar. We zien dat het 7/4/2010 is.
Trek nu 40356 (het gehele getal voor 27-6-2010, de laatste zondag van het vorige fiscale jaar) af van deze waarde om het aantal dagen sinds het begin van de dagen in de tabel Calendar te verkrijgen, zoals hier:
=INTEGER([datum]-40356)
Deel het resultaat vervolgens door 7 (dagen in een week):
=INTEGER(([datum]-40356)/7)
Het resultaat ziet er zo uit:
Period
De periode in deze aangepaste kalender bevat 28 dagen en begint altijd op een zondag. Deze kolom retourneert het nummer van de periode die begint op de eerste zondag van het eerste fiscale jaar.
=INTEGER(([Week]+3)/4)
Laten we deze formule eens nader bekijken.
Eerst maken we een formule die een waarde uit de kolom Week als geheel getal retourneert, zoals hieronder:
= INTEGER([Week])
Tel er vervolgens 3 bij op, zoals u hier ziet:
=INTEGER([Week]+3)
Deel het resultaat vervolgens door 4, zoals u hier ziet:
=INTEGER(([Week]+3)/4)
Het resultaat ziet er zo uit:
Periode Fiscaal jaar
Deze waarde geeft het fiscale jaar voor een bepaalde periode als resultaat.
=INTEGER(([Periode]+12)/13)+2008
Laten we deze formule eens nader bekijken.
We maken eerst een formule die een waarde uit Periode retourneert en 12 daarbij optelt:
=([Periode]+12)
We delen de uitkomst door 13, omdat er dertien perioden van 28 dagen zijn in het fiscale jaar:
=(([Periode]+12)/13)
We voegen 2010 toe, omdat dit het eerste jaar in de tabel is:
=(([Periode]+12)/13)+2010
Ten slotte gebruiken we de functie INTEGER om een breuk van het resultaat te verwijderen en een geheel getal te retourneren, gedeeld door 13, zoals hier:
= INT(([Periode]+12)/13)+2010
Het resultaat ziet er zo uit:
Periode in boekjaar
Met deze waarde wordt het periodegetal 1 tot 13 als resultaat gegeven, beginnend met de eerste volledige periode (die op zondag begint) van elk fiscaal jaar.
=ALS(REST([Periode];13); REST([Periode];13);13)
Deze formule is iets ingewikkelder, dus we zullen deze eerst beschrijven in een taal die we beter begrijpen. Met deze formule wordt aangegeven: deel de waarde van [Period] door 13 om een periodenummer (1-13) in het jaar te krijgen. Als dat getal 0 is, wordt 13 geretourneerd.
We maken eerst een formule waarmee de rest van de waarde uit Periode op 13 wordt geretourneerd. We kunnen de MOD (wiskundige en trigonometrische functies) als volgt gebruiken:
= REST ([punt],13)
Dit geeft ons grotendeels het gewenste resultaat, behalve wanneer de waarde voor Periode 0 is omdat deze datums niet binnen het eerste fiscale jaar vallen, zoals in de eerste vijf dagen van onze voorbeeldtabel Calendar Date. Dit kunnen wij verzorgen met een ALS-functie. Als het resultaat 0 is, wordt 13 als volgt geretourneerd:
= ALS(REST([Periode];13);REST([Periode];13);13)
Het resultaat ziet er zo uit:
Voorbeelddraaitabel
Hieronder ziet u een draaitabel met het veld SalesAmount uit de feitentabel Sales in VALUES, en de velden PeriodFiscalYear en PeriodInFiscalYear uit de dimensietabel Calendar-datum in ROWS. SalesAmount wordt voor de context samengevoegd per fiscaal jaar en de periode van 28 dagen in het fiscale jaar.
Relaties
Nadat u een datumtabel in uw gegevensmodel hebt gemaakt, moet u een relatie maken tussen de feitentabel met uw transactiegegevens en de datumtabel als u in uw gegevens in draaitabellen en rapporten wilt gaan bladeren en gegevens wilt aggregeren op basis van de kolommen in uw datumdimensietabel.
Omdat u een relatie moet maken op basis van datums, moet u ervoor zorgen dat u die relatie maakt tussen kolommen met waarden van het gegevenstype datetime (Date).
Voor elke datumwaarde in de feitentabel moet de gerelateerde opzoekkolom in de datumtabel overeenkomende waarden bevatten. Een rij (transactierecord) in de tabel met feiten over verkoop met de waarde 15-8-2012 12:00 uur in de kolom DateKey moet bijvoorbeeld een corresponderende waarde hebben in de gerelateerde kolom Date in de datumtabel (genaamd Calendar). Dit is een van de belangrijkste redenen waarom u wilt dat de datumkolom in de datumtabel een aaneengesloten datumbereik bevat waarin alle mogelijke datums in de feitentabel zijn opgenomen.
Opmerking
Hoewel de datumkolom in elke tabel van hetzelfde gegevenstype (datum) moet zijn, doet de indeling van elke kolom er niet toe.
Opmerking
Als u in Power Pivot geen relaties kunt maken tussen de twee tabellen, is het mogelijk dat in de datumvelden de datum en tijd niet met dezelfde precisie worden opgeslagen. Afhankelijk van de opmaak van de kolom kunnen de waarden er hetzelfde uitzien, maar anders worden opgeslagen. Lees meer over werken met de tijd.
Opmerking
Vermijd het gebruik van surrogaatsleutels voor geheel getal in relaties. Wanneer u gegevens uit een relationele gegevensbron importeert, worden datum- en tijdkolommen vaak voorgesteld door een surrogaatsleutel, een kolom voor gehele getallen die wordt gebruikt om een unieke datum aan te duiden. In Power Pivot moet u voorkomen dat u relaties maakt met behulp van datum/tijd-sleutels voor gehele getallen en in plaats daarvan kolommen gebruiken die unieke waarden met het gegevenstype Datum bevatten. Hoewel het gebruik van vervangende sleutels wordt beschouwd als een aanbevolen procedure in traditionele datawarehouses, zijn de sleutels voor gehele getallen niet nodig in Power Pivot en kunnen ze het moeilijk maken om waarden in draaitabellen te groeperen op verschillende datumperioden.
Als u de fout krijgt dat de typen niet overeenkomen wanneer u een relatie probeert te maken, komt dit waarschijnlijk doordat de kolom in de feitentabel niet van het gegevenstype Datum is. Dit kan gebeuren wanneer in Power Pivot een niet-datum (meestal een tekstgegevenstype) niet automatisch kan worden geconverteerd naar een datumgegevenstype. U kunt de kolom nog wel gebruiken in de feitentabel, maar dan moet u de gegevens converteren met een DAX-formule in een nieuwe berekende kolom. Zie Datums van het gegevenstype Tekst converteren naar datumdatums verderop in de bijlage.
Meerdere relaties
In sommige gevallen kan het nodig zijn om meerdere relaties of meerdere datumtabellen te maken. Als er bijvoorbeeld meerdere datumvelden aanwezig zijn in de feitentabel Verkoop, zoals DateKey, Verzenddatum en Retourdatum, kunnen deze allemaal een relatie hebben met het veld Datum in de tabel Calendar-datum, maar slechts één daarvan kan een actieve relatie zijn. Aangezien DateKey in dit geval de datum van de transactie vertegenwoordigt, en dus de belangrijkste datum, kan dit het beste als actieve relatie dienen. De anderen hebben inactieve relaties.
Met de volgende draaitabel wordt de totale verkoop per boekjaar en fiscaal kwartaal berekend. Een meting met de naam Totale verkoop, met de formule Totale verkoop:=SOM([Verkoopbedrag]), wordt in WAARDEN geplaatst, terwijl de velden Boekjaar en Boekkwartaal uit de tabel Calendar-datum in RIJEN worden geplaatst.
Deze eenvoudige draaitabel werkt correct omdat we onze totale verkoop willen optellen op basis van de transactiedatum in DateKey. Onze meting Totale verkoop gebruikt de datums in DateKey en wordt opgeteld per fiscaal jaar en fiscaal kwartaal, omdat er een relatie is tussen DateKey in de tabel Verkoop en de kolom Date in de tabel Calendar datum.
Inactieve relaties
Maar wat als we onze totale verkoop niet zouden willen optellen op transactiedatum, maar op verzenddatum? Er is een relatie nodig tussen de kolom Verzenddatum in de tabel Verkoop en de kolom Date in de tabel Calendar. Als we die relatie niet maken, zijn onze aggregaties altijd gebaseerd op de transactiedatum. Er kunnen echter meerdere relaties zijn, ook al kan er maar één actief zijn, en omdat transactiedatum de belangrijkste is, krijgt deze de actieve relatie met de tabel Calendar.
In dit geval heeft Verzenddatum een inactieve relatie, dus elke maatformule die wordt gemaakt om gegevens te aggregeren op basis van verzenddatums, moet de inactieve relatie specificeren met behulp van de functie USERELATIONSHIP .
Omdat er bijvoorbeeld een inactieve relatie is tussen de kolom Verzenddatum in de tabel Verkoop en de kolom Date in de tabel Calendar, kunt u een meting maken waarmee de totale verkoop per verzenddatum wordt berekend. We gebruiken een formule zoals deze om de te gebruiken relatie op te geven:
Totale verkoop per verzenddatum:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Deze formule stelt eenvoudig: Bereken een som voor SalesAmount, maar filter door de relatie te gebruiken tussen de kolom Verzenddatum in de tabel Verkoop en de kolom Date in de tabel Calendar.
Als we nu een draaitabel maken en de meting Totale verkoop op verzenddatum in WAARDEN en Boekjaar en Fiscaal kwartaal op RIJEN plaatsen, zien we hetzelfde eindtotaal, maar alle andere totaalbedragen voor het fiscale jaar en het fiscale kwartaal verschillen omdat ze zijn gebaseerd op de verzenddatum en niet op de transactiedatum.
met 
Met inactieve relaties kunt u slechts één datumtabel gebruiken, maar dan moeten metingen (zoals Totale verkoop per verzenddatum) verwijzen naar de inactieve relatie in de formule. Er is nog een alternatief, namelijk het gebruik van meerdere datumtabellen.
Meerdere datumtabellen
Een andere manier om met meerdere datumkolommen in de feitentabel te werken, is meerdere datumtabellen te maken en afzonderlijke actieve relaties tussen deze tabellen te maken. Laten we nog eens naar het voorbeeld van de tabel Verkoop kijken. We hebben drie kolommen met datums waarop we gegevens kunnen aggregeren:
- Een DateKey met de verkoopdatum van elke transactie.
- Een verzenddatum - met de datum en tijd waarop de verkochte artikelen naar de klant zijn verzonden.
- Een ReturnDate – met de datum en tijd waarop een of meer geretourneerde items zijn ontvangen.
Vergeet niet dat het DateKey-veld met de transactiedatum het belangrijkst is. We zullen de meeste aggregaties uitvoeren op basis van deze datums, dus we willen zeker een relatie tussen de datums en de kolom Datum in de tabel Calendar. Als we geen inactieve relaties tussen Verzenddatum en Retourdatum en het veld Date in de tabel Calendar willen maken, waardoor er speciale maateenheidsformules nodig zijn, kunnen we extra datumtabellen maken voor verzenddatum en retourdatum. We kunnen dan actieve relaties tussen hen creëren.
In dit voorbeeld hebben we nog een datumtabel gemaakt met de naam Verzendkalender. Dit betekent natuurlijk ook dat u extra datumkolommen moet maken. Aangezien deze datumkolommen in een andere datumtabel staan, willen we ze een naam geven die ze onderscheidt van de kolommen in de tabel Calendar. We hebben bijvoorbeeld kolommen gemaakt met de naam ShipYear, ShipMonth, ShipQuarter, enzovoort.
Als we de draaitabel maken en de meetwaarde Totale verkoop in WAARDEN en VerzendFiscaleJaar en VerzendFiscaleQuarter in RIJEN plaatsen, zien we dezelfde resultaten als bij het maken van een inactieve relatie en een speciaal berekend veld Totale verkoop op verzenddatum.
Elk van deze benaderingen vereist een zorgvuldige overweging. Als u meerdere relaties met één datumtabel gebruikt, moet u mogelijk speciale metingen maken waarmee inactieve relaties worden doorgegeven met behulp van de functie USERELATIONSHIP. Aan de andere kant kan het maken van meerdere datumtabellen verwarrend zijn in een veldenlijst en omdat het gegevensmodel meer tabellen bevat, is er meer geheugen nodig. Experimenteer met wat voor u het beste werkt.
Eigenschap Datumtabel
Met de eigenschap Datumtabel worden de metagegevens ingesteld die nodig zijn om Time-Intelligence functies zoals TOTALYTD, PREVIOUSMONTH en DATESBETWEEN correct te laten werken. Wanneer een berekening met een van deze functies wordt uitgevoerd, weet de formule-engine van Power Pivot waar deze naartoe moet gaan om de benodigde datums op te halen.
Waarschuwing
Als deze eigenschap niet is ingesteld, leveren metingen die DAX Time-Intelligence-functies gebruiken mogelijk niet de juiste resultaten op.
Wanneer u de eigenschap Datumtabel instelt, geeft u hierin een datumtabel en een datumkolom van het gegevenstype Datum (datum/tijd) op.
Procedure: De eigenschap Datumtabel instellen
- Selecteer de tabel Calendar in het PowerPivot-venster.
- Klik op het tabblad Ontwerpen op Als datumtabel markeren.
- Selecteer in het dialoogvenster Als datumtabel markeren een kolom met unieke waarden en het gegevenstype Datum.
Werken met de tijd
Alle datumwaarden met het gegevenstype Datum in Excel of SQL Server zijn eigenlijk een getal. In dat getal zitten cijfers die naar een tijd verwijzen. In veel gevallen is die tijd voor elke rij middernacht. Als bijvoorbeeld een DateTimeKey-veld in een feitentabel over de verkoop waarden heeft zoals 19-10-2010 12:00:00 AM, betekent dit dat de waarden betrekking hebben op het precisieniveau van de dag. Als aan de waarden in de DateTimeKey-velden een tijd is opgenomen, bijvoorbeeld 19-10-2010 8:44:00 AM, betekent dit dat de waarden tot op het minuut nauwkeurige niveau zijn. Waarden kunnen ook betrekking hebben op de precisie van het uurniveau of zelfs op seconden. De mate van precisie van de tijdwaarde is van grote invloed op de manier waarop u de datumtabel maakt en de relaties tussen de tabel en de feitentabel.
U moet bepalen of u uw gegevens gaat aggregeren met een nauwkeurigheid van een dag of met een precisie van een tijd. Met andere woorden, u kunt kolommen in de datumtabel zoals Ochtend, Middag of Uur gebruiken als tijd-datumvelden in de rij-, kolom- of filtergebieden van een draaitabel.
Opmerking
Dagen zijn de kleinste tijdseenheid waarmee tijdintelligente functies van DAX kunnen werken. Als u niet met tijdwaarden hoeft te werken, kunt u het beste de nauwkeurigheid van uw gegevens verminderen en dagen als minimumeenheid gebruiken.
Als u de gegevens wilt aggregeren tot op tijdniveau, moet de datumtabel een datumkolom bevatten inclusief de tijd. In feite heeft het een datumkolom nodig met een rij voor elk uur, of misschien zelfs elke minuut, van elke dag, voor elk jaar in het datumbereik. Dit komt omdat u overeenkomende waarden moet hebben als u een relatie wilt maken tussen de kolom DateTimeKey in de tabel feiten en de kolom datum in de datumtabel. U kunt zich voorstellen dat als u veel jaren toevoegt, dit een zeer grote datumtabel kan opleveren.
In de meeste gevallen wilt u uw gegevens echter alleen voor de dag aggregeren. Met andere woorden: u gebruikt kolommen zoals Jaar, Maand, Week of Dag van de week als velden in de rij-, kolom- of filtergebieden van een draaitabel. In dit geval hoeft de datumkolom in de datumtabel maar één rij te bevatten voor elke dag in een jaar, zoals eerder is beschreven.
Als de datumkolom een precisieniveau voor de datum bevat, maar u alleen tot op een dagniveau aggregeert, moet u de feitentabel mogelijk aanpassen door een nieuwe kolom te maken waarin de waarden in de datumkolom worden afgekapt tot een dagwaarde. Met andere woorden, converteer een waarde als 19-10-2010 08:44:00 naar 19-10-2010 012:00:00. Vervolgens kunt u de relatie tussen deze nieuwe kolom en de datumkolom in de datumtabel leggen omdat de waarden overeenkomen.
Laten we even naar een voorbeeld kijken. In deze afbeelding wordt een kolom DateTimeKey weergegeven in de feitentabel Verkoop. Alle aggregaties voor de gegevens in deze tabel hoeven alleen op dagniveau te worden toegepast door kolommen in de Calendar-datumtabel te gebruiken, zoals Jaar, Maand, Kwartaal enzovoort. De tijd die in de waarde is opgenomen is niet relevant, alleen de werkelijke datum.
Omdat we deze gegevens niet tot op tijdniveau hoeven te analyseren, hoeven we in de kolom Datum in de datumtabel van Calendar geen rij op te nemen voor elk uur en elke minuut van elke dag in elk jaar. De datumkolom in de datumtabel ziet er dus als volgt uit:
Als u een relatie wilt maken tussen de kolom DateTimeKey in de tabel Sales en de kolom Date in de tabel Calendar, kunnen we een nieuwe berekende kolom maken in de feitentabel Sales en de functie TRUNC gebruiken om de datum- en tijdwaarden in de kolom DateTimeKey af te kappen tot een datumwaarde die overeenkomt met de waarden in de kolom Date in de tabel Calendar. De formule ziet er zo uit:
=TRUNC([DateTimeKey],0)
Hierdoor krijgen we een nieuwe kolom (we hebben de naam DateKey) met de datum uit de kolom DateTimeKey en een tijd van 12:00:00 AM voor elke rij:
Nu kunnen we een relatie leggen tussen deze nieuwe kolom (DateKey) en de kolom Date in de tabel Calendar.
Op dezelfde manier kunnen we een berekende kolom maken in de tabel Verkoop die de tijdsprecisie in de kolom DateTimeKey reduceert tot het uurprecisieniveau. In dit geval werkt de functie TRUNC niet, maar kunnen we nog wel andere DAX-datum- en -tijdfuncties gebruiken om een nieuwe waarde op te halen en opnieuw samen te voegen tot een uurprecisie. We kunnen dan een formule als volgt gebruiken:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
De nieuwe kolom ziet er zo uit:
Als de kolom Datum in de datumtabel waarden heeft met een precisie van het uurniveau, kunnen we hier een relatie tussen maken.
Datums bruikbaarder maken
Veel van de datumkolommen die u in de datumtabel maakt, zijn nodig voor andere velden, maar zijn niet zo handig voor analyses. Het veld DateKey in de tabel Verkoop waarnaar in dit artikel wordt verwezen en dat in dit artikel wordt weergegeven, is bijvoorbeeld belangrijk omdat voor elke transactie die transactie wordt geregistreerd als een transactie op een bepaalde datum en tijd. Maar vanuit het oogpunt van analyse en rapportage is het helemaal niet zo nuttig omdat we het niet kunnen gebruiken als een rij-, kolom- of filterveld in een draaitabel of rapport.
Evenzo is in ons voorbeeld de kolom Date in de tabel Calendar erg handig, zelfs cruciaal, maar u kunt deze niet gebruiken als een dimensie in een draaitabel.
Om tabellen en de kolommen daarin zo bruikbaar mogelijk te houden en het gebruik van draaitabellen of Power View-rapporten te vergemakkelijken, is het belangrijk overbodige kolommen te verbergen voor clienthulpprogramma's. Het is mogelijk dat u ook bepaalde tabellen wilt verbergen. De eerder getoonde tabel Feestdagen bevat vakantiedatums die belangrijk zijn voor bepaalde kolommen in de tabel Calendar, maar u kunt de kolommen Datum en Feestdagen zelf niet als velden in een draaitabel gebruiken. Ook hier kunt u de hele tabel Feestdagen verbergen om de navigatie in de veldenlijsten te vergemakkelijken.
Een ander belangrijk aspect van het werken met datums is het gebruik van naamgevingsconventies. U kunt tabellen en kolommen in Power Pivot een naam geven die u maar wilt. Houd er echter rekening mee dat, met name als u de werkmap gaat delen met andere gebruikers, een goede naamconventie het gemakkelijker maakt om tabellen en datums te identificeren, niet alleen in lijsten met velden, maar ook in Power Pivot en in DAX-formules.
Als u een datumtabel in het gegevensmodel hebt, kunt u metingen gaan maken waarmee u optimaal gebruik kunt maken van uw gegevens. Sommige zijn zo eenvoudig als het optellen van de verkooptotalen voor het lopende jaar, andere kunnen ingewikkelder zijn, waarbij u moet filteren op een bepaald bereik van unieke datums. Zie Maateenheden in Power Pivot - en Time Intelligence-functies, voor meer informatie.
Bijlage
Datums van het gegevenstype Tekst converteren naar datums
In sommige gevallen bevat een feitentabel met transactiegegevens datums van het gegevenstype Tekst. Dat wil zeggen, een datum die wordt weergegeven als 2012-12-04T11:47:09 is in feite helemaal geen datum, of in ieder geval niet het type datum dat Power Pivot kan begrijpen. Het is eigenlijk gewoon tekst die leest als een datum. Als u een relatie wilt maken tussen een datumkolom in de feitentabel en een datumkolom in een datumtabel, moeten beide kolommen van het gegevenstype Datum zijn.
Wanneer u het gegevenstype voor een kolom met datums met het gegevenstype Tekst probeert te wijzigen in een datumgegevenstype, kan Power Pivot de datums interpreteren en automatisch converteren naar een gegevenstype dat echt is. Als PowerPivot een gegevenstypeconversie niet kan uitvoeren, krijgt u de fout dat de typen niet overeenkomen.
U kunt de datums echter wel converteren naar gegevens die echt zijn voor de datum. U kunt een nieuwe berekende kolom maken en een DAX-formule gebruiken om het jaar, de maand, de dag, de tijd, enzovoort, van de teksttekenreeksen te parseren en deze vervolgens weer samen te voegen op een manier die door Power Pivot als een echte datum kan worden gelezen.
In dit voorbeeld hebben we een feitentabel met de naam Verkopen in Power Pivot geïmporteerd. De kolom bevat een kolom met de naam DateTime. De waarden worden als volgt weergegeven:
Als we kijken naar Gegevenstype in het tabblad Start van de groep Opmaak in Power Pivot, zien we dat dit het gegevenstype Tekst is.
We kunnen geen relatie maken tussen de kolom DateTime en de kolom Date in de datumtabel omdat de gegevenstypen niet overeenkomen. Als we proberen het gegevenstype te wijzigen in Datum, krijgen we de foutmelding dat het type niet overeenkomt:
In dit geval kan Power Pivot het gegevenstype niet converteren van tekst naar datum. We kunnen deze kolom nog steeds gebruiken, maar om de kolom het gegevenstype Datum te geven, moeten we een nieuwe kolom maken waarin de tekst wordt geparseerd en omgezet in een waarde Power Pivot kan een datumgegevenstype maken.
Onthoud dat uit de sectie Werken met tijd eerder in dit artikel; Tenzij het nodig is dat uw analyse op een nauwkeurig tijdstip, moet u datums in uw feitentabel converteren naar een nauwkeurigheidsniveau voor dagen. Met dat in gedachten willen we dat de waarden in onze nieuwe kolom het nauwkeurigheidsniveau van de dag zijn (exclusief tijd). Met de volgende formule kunnen we de waarden in de kolom DateTime converteren naar een datumgegevenstype en het tijdniveau van precisie verwijderen:
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
Hierdoor krijgen we een nieuwe kolom (in dit geval de naam Datum). Power Pivot detecteert zelfs dat de waarden datums zijn en stelt het gegevenstype automatisch in op Datum.
Als we het tijdniveau van precisie willen behouden, breiden we de formule gewoon uit met de uren, minuten en seconden.
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +
TIME(DEEL([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))
Nu we een datumkolom van het gegevenstype datum hebben, kunnen we een relatie leggen tussen deze kolom en een datumkolom in een datum.
Aanvullende bronnen
QuickStart: informatie over de grondbeginselen van DAX in 30 minuten