Datumtabellen begrijpen en maken in Power Pivot in Excel

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 heel eenvoudig te doen en biedt u veel flexibiliteit.

Wanneer u een datumtabel maakt in Excel, begint u met één kolom met een aaneengesloten datumbereik. Vervolgens kunt u 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 geexemplaard, 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 sectie Datumtabel verder in dit artikel.

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. Dit is ook nodig voor het maken van een relatie tussen de feitentabel en de datumtabel. Maar die kolom met één datum met één rij voor elke dag is niet handig bij het analyseren op datums in een draaitabel of Power View rapport. U wilt dat de datumtabel kolommen bevat die u helpen uw gegevens te aggregeren voor een bereik of groep datums. U kunt bijvoorbeeld de verkoopbedragen per maand of kwartaal optelen, 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 uw 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 wijzigen of extra datumkolommen maken. Dit geldt vooral als u uw eigen datumtabel maakt in Excel en kopieert naar het gegevensmodel. Gelukkig is het maken van nieuwe datumkolommen in Power Pivot heel eenvoudig met datum- en tijdfuncties in DAX.

DAX-functies Datum en tijd

Als u ooit hebt gewerkt met datum- en tijdfuncties in Excel formules, bent u waarschijnlijk bekend met de functies Datum en tijd. Hoewel deze functies vergelijkbaar zijn met hun tegenhangers in Excel, zijn er enkele belangrijke verschillen:

• DaX-functies Datum en tijd gebruiken een datumtijdgegevenstype.

• Ze kunnen waarden uit een kolom als argument nemen.

• Ze kunnen worden gebruikt om datumwaarden te retourneren en/of te manipuleren.

Deze functies worden vaak gebruikt bij het maken van aangepaste datumkolommen in een datumtabel, dus ze zijn belangrijk om te begrijpen. We gebruiken een aantal van deze functies om kolommen te maken voor jaar, kwartaal, fiscale maand, en ga zo maar door.

DAX bevat de volgende functies Datum en tijd:

• DATUM

• DATUMWAARDE

• VOLGENDE DAG

• EDATE

• EOMONTH

• UUR

• MINUUT

• MAAND

• NU

• SECOND

• TIJD

• TIJDWAARDE

• VANDAAG

• WEEKDAG

• WEEKNUMMER

• JAAR

• YEARFRAC

Er zijn ook veel andere DAX-functies die u in uw formules kunt gebruiken. Veel van de formules die hier worden beschreven, gebruiken bijvoorbeeld Wiskundige en Trigonometrische functies zoals MOD en TRUNC,Logische functies zoals ALSen Tekstfuncties zoals OPMAAK Voor meer informatie over andere DAX-functies, zie de sectie Aanvullende bronnen verderop in dit artikel.

Formulevoorbeelden voor een kalenderjaar

In de volgende voorbeelden worden formules beschreven die worden gebruikt om extra kolommen te maken in een datumtabel met de naam Agenda. Eén kolom met de naam Datum bestaat al en bevat een aaneengesloten datumbereik van 1-1-2010 tot en met 31-12-2016.

Maandnaam

=NOTATIE([datum],"mmmm")

In deze formule gebruiken we de functie OPMAAK om een numerieke waarde te converteren van de kolom Datum naar tekst om de naam van de maand op te halen. We geven de kolom Datum op als het eerste argument en vervolgens de opmaak. We willen dat onze maandnaam alle tekens wen, dus gebruiken we 'mmmm'. Dit levert het volgende resultaat op:

Als we de naam van de maand, afgekort tot drie letters, willen retourneren, gebruiken we 'mmm' in het argument Opmaak.

Dag van de week

=OPMAAK([datum],"ddd")

In deze formule gebruiken we de functie OPMAAK om de naam van de dag op te halen. Omdat we alleen een verkorte dagnaam willen, geven we 'ddd' op in het argument Opmaak.

Voorbeelddraaitabel

Als 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 tabel Verkoop feit in WAARDEN en Jaar en Kwartaal uit de dimensietabel Agenda in RIJEN. SalesAmount wordt samengevoegd voor jaar- en kwartaalcontext.

Formulevoorbeelden voor een fiscaal jaar

Feestdagen of speciale datums

Mogelijk wilt u een datumkolom opnemen die aangeeft dat bepaalde datums feestdagen of een andere speciale datum zijn. U kunt bijvoorbeeld de verkoopto totalen voor nieuwjaarsdag optellen door een veld Feestdagen toe te voegen aan een draaitabel, als slicer of filter. In andere gevallen wilt u deze datums mogelijk uitsluiten van andere datumkolommen of in een meting.

Feestdagen of speciale dagen is heel eenvoudig. U kunt een tabel maken in Excel met de datums die u wilt opnemen. Vervolgens kunt u Toevoegen aan gegevensmodel kopiëren of gebruiken om deze als gekoppelde tabel toe te voegen aan het gegevensmodel. In de meeste gevallen is het niet nodig om een relatie te maken tussen de tabel en de tabel Agenda. Formules die ernaar verwijzen, kunnen de functie OPZOEKWAARDE gebruiken om waarden te retourneren.

Hieronder vindt u een voorbeeld van een tabel die is gemaakt in Excel met feestdagen die moeten worden toegevoegd aan de datumtabel:

In de datumtabel maken we een kolom met de naam Holiday en gebruiken we een formule als deze:

=OPZOEKWAARDE(Feestdagen[Feestdagen],Feestdagen[datum],Agenda[datum])

Laten we deze formule zorgvuldiger bekijken.

We gebruiken de functie OPZOEKWAARDE om waarden op te halen uit de kolom Vakantie in de tabel Feestdagen. In het eerste argument geven we de kolom op waar de resultaatwaarde zich zal vinden. We geven de kolom Vakantie op in de tabel Feestdagen, omdat dit de waarde is die we als resultaat willen geven.

=OPZOEKWAARDE(Feestdagen[Feestdag],Feestdagen[datum],Agenda[datum])

Vervolgens geven we het tweede argument op, de zoekkolom met de datums die we willen zoeken. We geven de kolom Datum op in de tabel Feestdagen, zoals dit:

=OPZOEKWAARDE(Feestdagen[Feestdagen],Feestdagen[datum],Agenda[datum])

Ten slotte geven we de kolom in onze agendatabel op met de datums die we willen zoeken in de tabel Vakantie. Dit is natuurlijk de kolom Datum in de tabel Agenda.

=OPZOEKWAARDE(Feestdagen[Feestdagen],Feestdagen[datum],Agenda[datum])

De kolom Vakantie retourneert de naam van de feestdagen voor elke rij met een datumwaarde die overeenkomt met een datum in de tabel Feestdagen.

Aangepaste agenda - dertien perioden van vier weken

Sommige organisaties, zoals detailhandel of foodservice, rapporteren vaak over verschillende perioden, zoals dertien perioden van vier weken. Met een kalender van dertien perioden van vier weken is elke periode 28 dagen; Daarom bevat elke periode vier maandagen, vier dinsdagen, vier woensdagen, en zo verder. Elke periode bevat hetzelfde aantal dagen en feestdagen vallen meestal elk jaar binnen dezelfde periode. U kunt ervoor kiezen om een punt te starten op elke dag van de week. Net zoals met datums in een agenda of fiscaal jaar, kunt u DAX gebruiken om extra kolommen met aangepaste datums te maken.

In de onderstaande voorbeelden begint de eerste volledige periode op de eerste zondag van het fiscale jaar. In dit geval begint het fiscale jaar op 7/1.

Period

De periode in deze aangepaste agenda bevat 28 dagen en deze begint altijd op een zondag. In deze kolom wordt het aantal van de periode die begint met de eerste zondag in het eerste fiscale jaar als retournatie.

=INT(([Week]+3)/4)

Laten we deze formule zorgvuldiger bekijken.

Eerst maken we een formule die een waarde uit de kolom Week als geheel getal retourneert, zoals dit:

=INT([Week])

Voeg vervolgens 3 toe aan die waarde, zoals dit:

=INT([Week]+3)

Deel vervolgens het resultaat door 4, zoals dit:

=INT(([Week]+3)/4)

Het resultaat ziet er als volgende uit:

Fiscale jaar periode

Deze waarde retourneert het fiscale jaar voor een periode.

=INT(([Punt]+12)/13)+2008

Laten we deze formule zorgvuldiger bekijken.

Eerst maken we een formule die een waarde uit Punt retourneert en 12 toevoegt:

= ([Punt]+12)

We delen het resultaat door 13, omdat er dertien perioden van 28 dagen in het fiscale jaar zijn:

=(([Punt]+12)/13)

We voegen 2010 toe, omdat dit het eerste jaar in de tabel is:

=(([Punt]+12)/13)+2010

Ten slotte gebruiken we de functie INT om een deel van het resultaat te verwijderen en een geheel getal als resultaat te retourneren, wanneer gedeeld door 13, zoals dit:

=INT(([Punt]+12)/13)+2010

Het resultaat ziet er als volgende uit:

Periode in FiscalYear

Deze waarde retourneert het puntnummer 1 – 13, te beginnen met de eerste volledige periode (vanaf zondag) in elk fiscaal jaar.

=ALS(MOD([Punt],13), MOD([Periode],13);13)

Deze formule is iets complexer, dus we zullen deze eerst beschrijven in een taal die we beter begrijpen. In deze formule wordt de waarde van [Punt] door 13 verdeeld om een puntnummer (1-13) in het jaar te krijgen. Als dat getal 0 is, retournt u 13.

Eerst maken we een formule die de rest van de waarde van Punt door 13 retourneert. We kunnen de mod (wiskundige en trigonometrische functies) als volgende gebruiken:

=MOD([Punt],13)

Dit geeft ons meestal het beste 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 ons voorbeeld Kalenderdatumtabel. We kunnen dit doen met een ALS-functie. Als het resultaat 0 is, retourneren we 13, zoals dit:

=ALS(MOD([Punt],13),MOD([Punt],13);13)

Het resultaat ziet er als volgende uit:

Meerdere relaties

In sommige gevallen kan het nodig zijn om meerdere relaties te maken of meerdere datumtabellen te maken. Als er bijvoorbeeld meerdere datumvelden in de tabel Verkoop fact zijn, zoals DateKey, ShipDate en ReturnDate, kunnen ze allemaal relaties hebben met het veld Datum in de tabel Agendadatum, maar slechts één van deze velden kan een actieve relatie zijn. In dit geval, omdat DateKey de datum van de transactie vertegenwoordigt, en dus de belangrijkste datum, kan dit het beste fungeren als de actieve relatie. De andere hebben inactieve relaties.

De volgende draaitabel berekent de totale omzet per fiscaal jaar en fiscaal kwartaal. Een meting met de naam Totale verkoop, met de formule Totale verkoop:=SOM([SalesAmount]), wordt in WAARDEN geplaatst en de velden FiscalYear en FiscalQuarter uit de tabel Kalenderdatum worden in RIJEN geplaatst.

Deze draaitabel met rechte termijn werkt correct omdat we onze totale omzet willen optelen op de transactiedatumin 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 Datum in de tabel Agendadatum.

Inactieve relaties

Maar wat als we onze totale verkoop niet per transactiedatum, maar per verzenddatum willen optelen? We hebben een relatie nodig tussen de kolom ShipDate in de tabel Verkoop en de kolom Datum in de tabel Agenda. Als we die relatie niet maken, zijn onze aggregaties altijd gebaseerd op de transactiedatum. We kunnen echter meerdere relaties hebben, ook al kan er maar één actief zijn en omdat de transactiedatum het belangrijkst is, krijgt deze de actieve relatie met de tabel Agenda.

In dit geval heeft ShipDate een inactieve relatie, dus elke maatformule die is gemaakt voor het aggregeren van gegevens op basis van verzenddatums, moet de inactieve relatie opgeven met de functie USERELATIONSHIP.

Omdat er bijvoorbeeld een inactieve relatie is tussen de kolom Verzenddatum in de tabel Verkoop en de kolom Datum in de tabel Agenda, kunnen we een meting maken die de totale verkoop per verzenddatum optelt. We gebruiken een formule als deze om de relatie op te geven die moet worden gebruikt:

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

In deze formule wordt eenvoudig het volgende gezegd: Bereken een som voor SalesAmount, maar filter op basis van de relatie tussen de kolom ShipDate in de tabel Verkoop en de kolom Datum in de tabel Agenda.

Als we nu een draaitabel maken en de totale verkoop per verzenddatum meten in WAARDEN en fiscaal jaar en fiscaal kwartaal op RIJEN, zien we hetzelfde eindtotaal, maar alle andere sombedragen voor het fiscale jaar en fiscale kwartaal verschillen omdat ze zijn gebaseerd op de verzenddatum en niet op de transactiedatum.

Als u inactieve relaties gebruikt, kunt u slechts één datumtabel gebruiken, maar vereist dit wel dat alle metingen (zoals Totale verkoop per verzenddatum) verwijzen naar de inactieve relatie in de formule. Er is nog een alternatief, dat wil zeggen dat u meerdere datumtabellen gebruikt.

Meerdere datumtabellen

Een andere manier om met meerdere datumkolommen in uw feitentabel te werken, is door meerdere datumtabellen te maken en afzonderlijke actieve relaties tussen deze kolommen te maken. Laten we het voorbeeld van de tabel Verkoop nog eens bekijken. We hebben drie kolommen met datums waarop we gegevens kunnen aggregeren:

• Een DateKey met de verkoopdatum voor elke transactie.

• Een verzenddatum: met de datum en tijd waarop de verkochte items naar de klant zijn verzonden.

• Een retourdatum: de datum en tijd waarop een of meer geretourneerde items zijn ontvangen.

Vergeet niet dat het veld DateKey met de transactiedatum het belangrijkst is. We zullen de meeste aggregaties doen op basis van deze datums, dus we willen zeker een relatie tussen deze datum en de kolom Datum in de tabel Agenda. Als we geen inactieve relaties willen maken tussen ShipDate en ReturnDate en het veld Datum in de tabel Agenda, waardoor speciale maatformules nodig zijn, kunnen we extra datumtabellen maken voor verzenddatum en retourdatum. We kunnen vervolgens actieve relaties tussen deze relaties maken.

In dit voorbeeld hebben we een andere datumtabel gemaakt met de naam ShipCalendar. Dit betekent natuurlijk ook het maken van extra datumkolommen. Omdat deze datumkolommen zich in een andere datumtabel hebben, willen we deze kolommen een naam geven die onderscheidt van dezelfde kolommen in de tabel Agenda. We hebben bijvoorbeeld kolommen gemaakt met de naam ShipYear, ShipMonth, ShipQuarter, en ga zo maar door.

Als we onze draaitabel maken en onze meting Totale verkoop in WAARDEN en ShipFiscalYear en ShipFiscalQuarter op RIJEN zetten, zien we dezelfde resultaten als toen we een inactieve relatie en een speciaal berekend veld Total Sales by Ship Date maakten.

Voor elk van deze benaderingen moet zorgvuldig worden gekeken. Wanneer u meerdere relaties met één datumtabel gebruikt, moet u mogelijk speciale metingen maken die inactieve relaties doorvoeren met de functie USERELATIONSHIP. Aan de andere kant kan het maken van meerdere datumtabellen verwarrend zijn in een lijst met velden en omdat er meer tabellen in het gegevensmodel staan, is er meer geheugen nodig. Experimenteer met wat het beste voor u werkt.

How to: De eigenschap Datumtabel instellen

1. Selecteer in het PowerPivot de tabel Agenda.

2. Klik op het tabblad Ontwerpen op Markeren als datumtabel.

3. Selecteer in het dialoogvenster Markeren als datumtabel een kolom met unieke waarden en het gegevenstype Datum.

Datums van tekstgegevenstype converteren naar een datumgegevenstype

In sommige gevallen kan een feitentabel met transactiegegevens datums van tekstgegevenstype bevatten. Dat wil zeggen dat een datum die wordt weergegeven als 2012-12-04T11:47:09 in feite helemaal geen datum is, of in ieder geval niet het type datum dat Power Pivot kan begrijpen. Het is eigenlijk alleen maar tekst die als een datum leest. 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 die een tekstgegevenstype zijn, probeert te wijzigen in een datumgegevenstype, kan Power Pivot de datums meestal interpreteren en automatisch converteren naar een gegevenstype met een werkelijke datum. Als Power Pivot geen gegevenstypeconversie kan doen, krijgt u een foutmelding voor typefouten.

U kunt de datums echter nog steeds converteren naar een gegevenstype echte datum. U kunt een nieuwe berekende kolom maken en een DAX-formule gebruiken om het jaar, de maand, de dag, de tijd enzovoort uit de tekenreeksen te parseren en deze vervolgens weer samen tevoegen op een manier waarop Power Pivot kan worden gelezen als een echte datum.

In dit voorbeeld hebben we een feitentabel met de naam Verkoop geïmporteerd in Power Pivot. De kolom bevat een kolom met de naam DateTime. Waarden worden als dit weergegeven:

Als we gegevenstype bekijken in het tabblad Start van de groep Opmaakgroep Power Pivot, zien we dat het gegevenstype Tekst is.

We kunnen geen relatie maken tussen de kolom DateTime en de kolom Datum in onze datumtabel omdat de gegevenstypen niet overeenkomen. Als we het gegevenstype proberen te wijzigen in Datum,krijgen we een fout voor typefouten:

In dit geval kon Power Pivot het gegevenstype niet converteren van tekst naar datum. We kunnen deze kolom nog steeds gebruiken, maar om deze in een gegevenstype met een echte datum te krijgen, moeten we een nieuwe kolom maken die de tekst parseert en deze opnieuw maakt tot een waarde die power pivot kan maken tot een datumgegevenstype.

Vergeet niet, uit de sectie Werken met tijd eerder in dit artikel; tenzij het nodig is dat uw analyse een precisieniveau voor de tijd van de dag is, moet u datums in de feitentabel converteren naar een dagniveau van precisie. Met dat in gedachten willen we dat de waarden in onze nieuwe kolom op het precisieniveau van de dag staan (met uitzondering van tijd). We kunnen zowel de waarden in de kolom DateTime converteren naar een datumgegevenstype als het tijdsprecisieniveau met de volgende formule verwijderen:

=DATUM(LINKS([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

Dit geeft ons een nieuwe kolom (in dit geval datum). Power Pivot detecteert zelfs de waarden die datums moeten zijn en stelt het gegevenstype automatisch in op Datum.

Als we het tijdsniveau van precisie willen behouden, breiden we de formule uit met de uren, minuten en seconden.

=DATUM(LINKS([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

TIJD(MID([DateTime],12;2), MID([DateTime],15,2), MID([DateTime],18;2))

Nu we een kolom Datum van het gegevenstype Datum hebben, kunnen we een relatie maken tussen deze kolom en een datumkolom in een datum.

Aanvullende bronnen

Datums in Power Pivot

Berekeningen in Power Pivot

QuickStart: informatie over de grondbeginselen van DAX in 30 minuten

Verwijzing naar expressies voor gegevensanalyse

DAX Resource Center