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

Wanneer u een datumtabel maakt in Excel, begint u met één kolom met een aaneengesloten datumbereik. U kunt vervolgens extra kolommen zoals Jaar, Kwartaal, Maand, Fiscaal jaar, Periode, enzovoort maken in het Excel-werkblad met behulp van Excel-formules. Nadat u de tabel naar het gegevensmodel hebt gekopieerd, kunt u deze als berekende kolommen maken. Het maken van extra datumkolommen in Power Pivot wordt beschreven in de sectie Nieuwe datumkolommen toevoegen aan de datumtabel verderop 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. 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 nuttig bij het analyseren op datums in een draaitabel of Power View-rapport. U wilt dat de datumtabel kolommen bevat waarmee u uw gegevens kunt aggregeren voor een bereik of groep datums. U kunt bijvoorbeeld verkoopbedragen optellen per maand of kwartaal, of u kunt een meting maken waarmee de jaar-op-jaargroei wordt berekend. In elk van deze gevallen heeft uw datumtabel jaar-, maand- of kwartaalkolommen nodig waarmee u uw gegevens voor die periode kunt samenvoegen.

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 kunt u een aantal van deze kolommen wijzigen of extra datumkolommen maken. Dit geldt met name als u uw eigen datumtabel in Excel maakt en deze naar het gegevensmodel kopieert. Gelukkig is het maken van nieuwe datumkolommen in Power Pivot heel eenvoudig met datum- en tijdfuncties in DAX.

DAX-datum- en tijdfuncties

Als u ooit met datum- en tijdfuncties in Excel-formules hebt gewerkt, 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 datum/tijd-gegevenstype.

• Ze kunnen waarden uit een kolom als argument nemen.

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

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 Year, Quarter, FiscalMonth, enzovoort.

DAX bevat de volgende datum- en tijdfuncties:

• DATUM

• DATUMWAARDE

• VOLGENDE DAG

• EDATE

• EOMONTH

• UUR

• MINUUT

• MAAND

• NU

• TWEEDE

• TIJD

• TIJDWAARDE

• VANDAAG

• WEEKDAG

• WEEKNUM

• JAAR

• JAARFRAC

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 ALS en tekstfuncties zoals FORMAT Zie de sectie Aanvullende resources verderop in dit artikel voor meer informatie over andere DAX-functies.

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

=FORMAT([date];"mmmm")

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

Als we de maandnaam willen retourneren die is afgekort tot drie letters, gebruiken we 'mmm' in het notatieargument.

Dag van de week

=FORMAT([date];"ddd")

In deze formule gebruiken we de functie FORMAT om de dagnaam op te halen. Omdat we alleen een verkorte dagnaam willen, geven we 'ddd' op in het notatieargument.

Voorbeelddraaitabel

Zodra 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 Sales fact in VALUES en Year en Quarter uit de dimensietabel Calendar in ROWS. SalesAmount wordt geaggregeerd voor jaar- en kwartaalcontext.

Formulevoorbeelden voor een fiscaal jaar

Feestdagen of speciale datums

U kunt een datumkolom opnemen die aangeeft dat bepaalde datums feestdagen of een andere speciale datum zijn. U kunt bijvoorbeeld de verkooptotalen voor nieuwjaarsdag optellen door een veld Feestdag toe te voegen aan een draaitabel, als 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. Vervolgens kunt u Toevoegen aan gegevensmodel kopiëren of gebruiken om het als een 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 waarnaar wordt verwezen, kunnen de functie LOOKUPVALUE gebruiken om waarden te retourneren.

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

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

=LOOKUPVALUE(Feestdagen[Feestdagen],Feestdagen[datum],Kalender[datum])

Laten we deze formule eens zorgvuldiger bekijken.

We gebruiken de functie LOOKUPVALUE om waarden op te halen uit de kolom Feestdagen in de tabel Feestdagen. In het eerste argument geven we de kolom op waar de resultaatwaarde zich bevindt. We geven de kolom Feestdagen op in de tabel Feestdagen , omdat dit de waarde is die we willen retourneren.

=LOOKUPVALUE(Feestdagen[Feestdagen],Feestdagen[datum],Kalender[datum])

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

=LOOKUPVALUE(Feestdagen[Feestdagen],Feestdagen[datum],Kalender[datum])

Ten slotte geven we de kolom in de tabel Agenda op met de datums waarnaar we willen zoeken in de tabel Feestdagen . Dit is natuurlijk de kolom Datum in de tabel Agenda .

=LOOKUPVALUE(Feestdagen[Feestdagen],Feestdagen[datum],Kalender[datum])

De kolom Feestdagen 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 dertien kalender van 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 normaal gesproken vallen feestdagen elk jaar binnen dezelfde periode. U kunt ervoor kiezen om een periode op elke dag van de week te starten. Net als bij datums in een kalender 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 boekjaar. In dit geval begint het fiscale jaar op 7/1.

Period

De periode in deze aangepaste kalender bestaat uit 28 dagen en begint altijd op een zondag. Deze kolom retourneert het nummer van de periode die begint met de eerste zondag in het eerste fiscale jaar.

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

Laten we deze formule eens zorgvuldiger bekijken.

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

=INT([Week])

Voeg vervolgens 3 toe aan die waarde, zoals deze:

=INT([Week]+3)

Deel het resultaat vervolgens door 4, zoals dit:

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

Het resultaat ziet er als volgt uit:

Periode fiscaal jaar

Deze waarde retourneert het fiscale jaar voor een bepaalde periode.

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

Laten we deze formule eens zorgvuldiger bekijken.

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

= ([Periode]+12)

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

=(([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 INT om een willekeurig deel van het resultaat te verwijderen en een geheel getal te retourneren, gedeeld door 13, zoals:

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

Het resultaat ziet er als volgt uit:

Periode in fiscaal jaar

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

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

Deze formule is iets complexer, dus we zullen deze eerst beschrijven in een taal die we beter begrijpen. Deze formule geeft aan dat u de waarde van [Punt] door 13 deelt om een periodenummer (1-13) in het jaar op te halen. Als dat getal 0 is, retourneert u 13.

Eerst maken we een formule die de rest van de waarde retourneert van Periode op 13. We kunnen de MOD (wiskundige en trigonometrische functies) als volgt gebruiken:

=MOD([Periode];13)

Dit geeft ons voor het grootste deel 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 de tabel Kalenderdatum. We kunnen dit regelen met een ALS-functie. Als ons resultaat 0 is, retourneren we 13, zoals:

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

Het resultaat ziet er als volgt 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 feitentabel Sales staan, zoals DateKey, ShipDate en ReturnDate, kunnen ze allemaal relaties hebben met het veld Datum in de tabel Kalenderdatum, maar slechts één daarvan kan een actieve relatie zijn. In dit geval, omdat DateKey de datum van de transactie vertegenwoordigt, en dus de belangrijkste datum, zou dit het beste als de actieve relatie dienen. De andere hebben inactieve relaties.

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

Deze lineaire draaitabel werkt correct omdat we onze totale verkoop willen bij elkaar optellen op de transactiedatumin DateKey. De meting Totale verkoop maakt gebruik van 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 Kalenderdatum.

Inactieve relaties

Maar wat gebeurt er als we onze totale verkoop niet op transactiedatum, maar op verzenddatum willen bij elkaar optellen? We hebben een relatie nodig tussen de kolom ShipDate in de tabel Sales en de kolom Date 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 slechts één actief zijn, en omdat transactiedatum de belangrijkste is, krijgt deze de actieve relatie met de tabel Agenda.

In dit geval heeft ShipDate een inactieve relatie, dus elke metingformule die is gemaakt om gegevens te aggregeren op basis van verzenddatums, moet de inactieve relatie opgeven met behulp van de functie USERELATIONSHIP .

Omdat er bijvoorbeeld een inactieve relatie is tussen de kolom ShipDate in de tabel Sales en de kolom Date in de tabel Agenda, kunnen we een meting maken waarmee de totale verkoop wordt opgeteld op verzenddatum. We gebruiken een formule zoals deze om de relatie op te geven die moet worden gebruikt:

Totale verkoop per verzenddatum:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Deze formule geeft alleen aan: Een som berekenen voor SalesAmount, maar filter met behulp van de relatie tussen de kolom ShipDate in de tabel Sales en de kolom Date in de tabel Calendar.

Als we nu een draaitabel maken en de meting Totale verkoop per verzenddatum in WAARDEN en Fiscaal jaar en Fiscaal kwartaal in RIJEN plaatsen, zien we hetzelfde eindtotaal, maar alle andere sombedragen voor het fiscale jaar en het 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 wel dat metingen (zoals Totale verkoop per verzenddatum) in de formule verwijzen naar de inactieve relatie. Er is nog een alternatief, namelijk het gebruik van meerdere datumtabellen.

Meerdere datumtabellen

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

• Een DateKey met de datum van verkoop voor 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 veld DateKey met de transactiedatum het belangrijkst is. We doen de meeste van onze aggregaties 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 Date in de tabel Calendar, waardoor speciale meetformules nodig zijn, kunnen we extra datumtabellen maken voor verzenddatum en retourdatum. We kunnen vervolgens actieve relaties tussen hen maken.

In dit voorbeeld hebben we een andere datumtabel gemaakt met de naam ShipCalendar. Dit betekent natuurlijk ook dat er extra datumkolommen moeten worden gemaakt. Omdat deze datumkolommen zich in een andere datumtabel bevinden, willen we ze een naam geven die hen onderscheidt van dezelfde kolommen in de tabel Agenda. We hebben bijvoorbeeld kolommen gemaakt met de namen ShipYear, ShipMonth, ShipQuarter, enzovoort.

Als we onze draaitabel maken en de meting Totale verkoop in WAARDEN en ShipFiscalYear en ShipFiscalQuarter op ROWS plaatsen, zien we dezelfde resultaten die we zagen toen we een inactieve relatie en een speciaal berekend veld Totale verkoop per verzenddatum maakten.

Elk van deze benaderingen vereist zorgvuldige overweging. Wanneer u meerdere relaties gebruikt met één datumtabel, moet u mogelijk speciale metingen maken waarmee inactieve relaties worden overgedragen met behulp van de functie USERELATIONSHIP. Aan de andere kant kan het maken van meerdere datumtabellen verwarrend zijn in een lijst met velden en omdat u meer tabellen in het gegevensmodel hebt, is er meer geheugen vereist. Experimenteer met wat het beste voor u werkt.

Procedure: de eigenschap Datumtabel instellen

1. Selecteer in het PowerPivot-venster 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 het gegevenstype tekst bevatten. Een datum die wordt weergegeven als 2012-12-04T11:47:09 is in feite helemaal geen datum, of ten minste niet het type datum dat Power Pivot kan begrijpen. Het is 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 die het gegevenstype tekst zijn, probeert te wijzigen in een datumgegevenstype, kan Power Pivot de datums interpreteren en automatisch converteren naar een gegevenstype waar. Als Power Pivot geen gegevenstypeconversie kan uitvoeren, krijgt u een fout die niet overeenkomt met het type.

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

In dit voorbeeld hebben we een feitentabel met de naam Verkoop geïmporteerd in Power Pivot. Deze bevat een kolom met de naam DateTime. Waarden zien er als volgt uit:

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

Er kan geen relatie worden gemaakt 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 een fout die niet overeenkomt met het type:

In dit geval kan Power Pivot het gegevenstype niet converteren van tekst naar datum. We kunnen deze kolom nog steeds gebruiken, maar om deze in een echt datumgegevenstype te krijgen, moeten we een nieuwe kolom maken waarmee de tekst wordt geparseerd en opnieuw wordt gemaakt in een waarde die Power Pivot een datumgegevenstype kan maken.

Vergeet niet uit de sectie Werken met tijd eerder in dit artikel; tenzij het noodzakelijk is dat uw analyse wordt uitgevoerd op een tijd-of-dag precisieniveau, moet u datums in uw feitentabel converteren naar een dag van precisie. Met dat in gedachten willen we dat de waarden in de nieuwe kolom op het dagprecisieniveau (exclusief tijd) staan. We kunnen de waarden in de kolom DateTime converteren naar een datumgegevenstype en het precisieniveau van de tijd verwijderen met de volgende formule:

=DATE(LEFT([DateTime];4), MID([DateTime],6,2), MID([DateTime];9,2))

Dit geeft ons een nieuwe kolom (in dit geval, met de naam Date). Power Pivot detecteert zelfs datumwaarden 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.

=DATE(LEFT([DateTime];4), MID([DateTime],6,2), MID([DateTime];9;2)) +

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

Nu we een datumkolom van het gegevenstype Datum hebben, kunnen we er een relatie tussen maken 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

Naslaginformatie over expressies voor gegevensanalyse

DAX Resource Center