Datumtabellen begrijpen en maken in Power Pivot in Excel

Opmerking: We willen u graag zo snel mogelijk de meest recente Help-inhoud in uw eigen taal bieden. Deze pagina is automatisch vertaald en kan grammaticale fouten of onnauwkeurigheden bevatten. Wij hopen dat deze inhoud nuttig voor u is. Kunt u ons onder aan deze pagina laten weten of de informatie nuttig voor u was? Hier is het Engelstalige artikel ter referentie.

Datumtabellen in de Power Pivot zijn essentieel voor bladeren door en berekenen van gegevens over de tijd. Dit artikel bevat een uitgebreide begrip van datumtabellen en hoe u ze kunt maken in Power Pivot. Met name in dit artikel wordt beschreven:

  • Waarom een datumtabel belangrijk is voor bladeren door en berekenen van gegevens op datum en tijd.

  • Klik hier voor meer informatie over het gebruik van Power Pivot een datumtabel toevoegen aan het gegevensmodel.

  • Het maken van nieuwe datumkolommen zoals jaar, maand en periode in een datumtabel.

  • Hoe u relaties maken tussen datumtabellen en feitentabellen.

  • Klik hier voor meer informatie over het werken met tijd.

In dit artikel is bedoeld voor gebruikers die eerder met Power Pivot. Maar het is belangrijk dat u hebt al een goed inzicht in het importeren van gegevens, relaties maken en maken van berekende kolommen en metingen.

In dit artikel niet wordt beschreven hoe DAX Time Intelligence-functies gebruiken in formules maateenheid. Zie voor meer informatie over het metingen maken met DAX Time Intelligence-functies Time Intelligence in Power Pivot in Excel.

Opmerking: In Power Pivot zijn de namen "maateenheid" en "berekend veld" synoniem. We gebruiken de maateenheid naam in dit artikel. Zie metingen in Power Pivotvoor meer informatie.

Inhoud

Datumtabellen

Een datumtabel toevoegen aan het gegevensmodel

Importeren uit een relationele database

Een datumtabel maken in Excel

Hoe u: een datumtabel maken in Excel en deze in het gegevensmodel kopiëren

Nieuwe datumkolommen toevoegen aan de datumtabel

Datum- en tijdfuncties

Formulevoorbeelden voor een kalenderjaar

Year

Maand

Kwartaal

De maandnaam van de

Voorbeelden van formules voor een fiscaal jaar

Fiscaal jaar

Fiscale maand

Fiscaal kwartaal

Feestdagen of speciale datums

Aangepaste kalender: 13 perioden van vier weken

Week

termijn

Fiscaal jaar voor periode

Periode in FiscalYear

Relaties

Meerdere relaties

Inactieve relaties

Meerdere datumtabellen

Eigenschap datumtabel

Werken met tijd

Datums eenvoudiger

Bijlage

Tekstgegevenstype converteren datums naar het datumgegevenstype

Aanvullende bronnen

Datumtabellen

Bijna alle gegevensanalyse heeft betrekking op Bladeren en gegevens over datums en tijden vergelijken. Zoals u mogelijk wilt optellen verkoopbedragen voor de afgelopen fiscaal kwartaal en vergelijk deze totalen met andere kwartalen, of wilt u mogelijk een maandafsluiting saldo voor een account berekenen. In elk van deze gevallen gebruikt u datums als een manier om te groeperen en aggregeren verkoop transacties of saldi voor een bepaalde periode in tijd.

Power View-rapport

Draaitabel voor totale verkoop per fiscaal kwartaal

Een datumtabel kan bevatten veel verschillende weergaven van datums en tijden. Een datumtabel hebben bijvoorbeeld vaak kolommen zoals fiscaal jaar, maand, kwartaal of periode die u kunt selecteren als velden uit een lijst met velden wanneer segmenteren en filteren van gegevens in draaitabellen of Power View-rapporten.

Lijst met Power View-velden

Lijst met Power View-velden

Voor datumkolommen zoals jaar, maand en kwartaal om op te nemen alle datums in de desbetreffende bereik, de datum tabel moet hebben ten minste één kolom met een aaneengesloten set datums. Deze kolom moet dat wil zeggen één rij voor elke dag voor elk jaar opgenomen in de datumtabel.

Bijvoorbeeld als de gegevens die u wilt bladeren datums vanaf 1e februari 2010 tot en met November 30e 2012 en u op een kalenderjaar rapport bevat, zult klikt u vervolgens u een datumtabel met ten minste een datumbereik van de 1e 2010 januari tot en met 31 December-2012. Elk jaar in de datumtabel moet alle dagen voor elk jaar bevatten. Als u wordt uw gegevens met nieuwere gegevens regelmatig worden bijgewerkt, wilt u mogelijk leeg de einddatum op een jaar of twee, zodat u niet hoeft aan de datumtabel werk volgens de tijd gaat door.

Datumtabel met een aaneengesloten set datums

Datumtabel met aaneengesloten datums

Als u op een fiscaal jaar, kunt u een datumtabel maken met een aaneengesloten set datums voor elk boekjaar. Als het fiscale jaar op 1 maart begint en u gegevens voor boekjaren 2010 omhoog tot en met de huidige datum (bijvoorbeeld in FJ 2013 hebt), kunt u bijvoorbeeld een datumtabel dat begint op 1-3-2009 en ten minste elke dag in elk boekjaar tot en met de laatste datum bevat maken in het fiscale jaar 2013.

Als u zowel kalenderjaar en fiscaal jaar wordt rapporteren, hoeft u niet om afzonderlijke datumtabellen te maken. Een tabel met één datum kunt kolommen opnemen voor een kalenderjaar, fiscale jaar en zelfs een 13 perioden vier weken periode agenda. Het belangrijkste is dat de datumtabel bevat een aaneengesloten set datums voor alle jaren opgenomen.

Een datumtabel toevoegen aan het gegevensmodel

Er zijn verschillende manieren waarop u kunt een datumtabel toevoegen aan uw gegevensmodel:

  • Importeren uit een relationele database of andere gegevensbron.

  • Een datumtabel maken in Excel en kopieer of koppelen aan een nieuwe tabel in Power Pivot.

  • Importeren uit Microsoft Azure Marketplace.

We kijken naar hierna uitgebreider nauwkeurig.

Importeren uit een relationele database

Als u sommige of alle gegevens vanuit een data-warehouse of ander type relationele database importeren, de kans groot dat er bestaat al een datumtabel en relaties tussen deze en de rest van de gegevens die u importeert. De datums en opmaak past waarschijnlijk de datums in uw gegevens faculteit en de datums waarschijnlijk ook in het verleden begint en Ga uiterst uitfaden in de toekomst. De datumtabel die u wilt importeren mogelijk erg groot en voorbij wat u moet opnemen in uw gegevensmodel van een datumbereik bevatten. Power Pivot de Wizard tabel importeren van Geavanceerd filter functies kunt u alleen de datums en wat u moet bepaalde kolommen selectief te kiezen. Dit kan aanzienlijk van uw werkmap verkleinen en de prestaties verbeteren.

Wizard tabel importeren

Wizard Tabel importeren

In de meeste gevallen moet u niet maken van eventuele extra kolommen zoals fiscaal jaar, Week, de naam van de maand, enzovoort, omdat ze al in de geïmporteerde tabel. Echter in sommige gevallen, nadat u de tabel date geïmporteerd in uw gegevensmodel, hebt u mogelijk moet maken extra datumkolommen, afhankelijk van een bepaalde rapportage nodig. Dit is gelukkig eenvoudig moet u doen met DAX. U wordt meer informatie over het maken van datum tabelvelden later. Elke omgeving is niet hetzelfde. Als u niet zeker weet of uw gegevensbronnen een gerelateerde datum of de tabel calendar hebben, met de databasebeheerder communiceren.

Een datumtabel maken in Excel

U kunt een datumtabel maken in Excel en kopieer deze naar een nieuwe tabel in het gegevensmodel. Dit is heel eenvoudig doen en geeft u een groot aantal flexibiliteit.

Wanneer u een datumtabel in Excel maken, begint u met één kolom met een aaneengesloten bereik van datums. U kunt extra kolommen zoals jaar, kwartaal, maand, fiscale jaar, termijn, enz. Klik in het Excel-werkblad maken met behulp van Excel-formules, of nadat u de tabel in het gegevensmodel hebt gekopieerd, kunt u ze als berekende kolommen maken. Aanvullende datumkolommen maken in Power Pivot wordt in de sectie Nieuwe datumkolommen toevoegen aan de Datumtabel verderop in dit artikel beschreven.

Hoe u: een datumtabel maken in Excel en deze in het gegevensmodel kopiëren

  1. Klik in Excel in een leeg werkblad in cel A1, typ een naam voor het koptekst van kolom aan te geven van een datumbereik. Dit is meestaliets zoals datum, DateTime of DateKey.

  2. In cel A2, typt u een begindatum. Bijvoorbeeld 1/1/2010.

  3. Klik op de vulgreep te slepen en sleep deze naar beneden af op een rij getal dat een einddatum bevat. Bijvoorbeeld 12/31/2016.

    Datumkolom in Excel

  4. Selecteer alle rijen in de kolom Date (inclusief de kopnaam in cel A1).

  5. In de groep stijlen op opmaken als tabelen selecteer een stijl.

  6. Klik in het dialoogvenster Opmaken als tabel op OK.

    Datumkolom in Power Pivot

  7. Kopieer alle rijen, inclusief de kop.

  8. In Power Pivot, klik op het tabblad Start op Plakken.

  9. Typ een naam, zoals de datum of een agendain Plakvoorbeeld > Tabelnaam . Laat het vakje eerste rij als kolomkoppeningeschakeld en klik vervolgens op OK.

    Plakvoorbeeld

    De nieuwe datumtabel (met de naam Calendar in dit voorbeeld) in Power Pivot ziet er zo uit:

    Datumtabel in Power Pivot

    Opmerking: U kunt ook een gekoppelde tabel maken met behulp van toevoegen aan gegevensmodel. Echter dit zorgt ervoor dat uw werkmap onnodig groot omdat de werkmap twee versies van de datumtabel heeft. een in Excel en een in Power Pivot...

Opmerking: De naam datum is een trefwoord in Power Pivot. Als u de tabel een naam geven u maken in Power Pivot datum en moet u de naam van de tabel met enkele aanhalingstekens insluiten in een DAX-formules die verwijzen naar deze in een argument. Alle voorbeeld afbeeldingen en formules in dit artikel raadpleegt u een datumtabel in Power Pivot met de naam Calendarhebt gemaakt.

U hebt nu een datumtabel in uw gegevensmodel. U kunt nieuwe datumkolommen zoals jaar, maand, enzovoort toevoegen met behulp van DAX.

Nieuwe datumkolommen toevoegen aan de datumtabel

Een datumtabel met een enkel datumkolom met één rij voor elke dag voor elk jaar is belangrijk om alle datums in een bepaald datumbereik te definiëren. Het is ook nodig zijn voor het maken van een relatie tussen de feitentabel en de tabel date. Maar die kolom één datum met één rij voor elke dag is niet handig bij het analyseren van door de datums in een draaitabel- of Power View-rapport. U wilt opnemen kolommen die u helpen aggregaat in uw datumtabel uw gegevens voor een bereik of de groep met datums. Bijvoorbeeld, wilt u mogelijk verkoopbedragen optellen per maand of kwartaal of u een maateenheid die wordt berekend jaar tot jaar groei mogelijk maakt. In elk van deze gevallen moet de datumtabel jaar, maand of kwartaal kolommen waarmee u kunt uw gegevens aggregeren voor die periode.

Als u de datumtabel geïmporteerd uit een relationele gegevensbron, deze mogelijk al beschikt over de verschillende soorten datumkolommen die u wilt. In sommige gevallen wilt u mogelijk sommige van deze kolommen wijzigen of extra datumkolommen maken. Dit geldt met name als u uw eigen datumtabel in Excel maken en kopieer deze naar het gegevensmodel. Gelukkig is het vrij eenvoudig met datum- en tijdfuncties in DAX nieuwe datumkolommen maken in Power Pivot.

Tip: Als u nog niet met een in DAX gewerkt hebt, is een prima plek om te leren starten Snelstartgids: grondbeginselen van DAX meer in 30 minuten op Office.com.

Datum- en tijdfuncties

Als u ooit met datum- en tijdfuncties in Excel-formules hebt gewerkt, klikt waarschijnlijk u vertrouwd met de datum- en tijdfuncties. Hoewel deze functies vergelijkbaar met hun collega's in Excel zijn, zijn er enkele belangrijke verschillen:

  • DAX-datum- en tijdfuncties datetime-gegevenstype gebruikt.

  • Waarden uit een kolom kunnen als argument nemen.

  • Ze kunnen worden geretourneerd en/of bewerken van datumwaarden worden gebruikt.

Deze functies worden gebruikt bij het maken van aangepaste datum-kolommen in een datumtabel, zodat ze zijn belangrijk om te begrijpen. We een getal van deze functies gebruiken om kolommen te maken voor het jaar, kwartaal, FiscalMonth, enzovoort.

Opmerking: Datum- en tijdfuncties in DAX zijn niet hetzelfde als Time Intelligence-functies. Meer informatie over Time Intelligence in Power Pivot in Excel 2013.

DAX bevat de volgende datum- en tijdfuncties:

Er zijn tal van andere DAX-functies die u ook in uw formules gebruiken kunt. Bijvoorbeeld veel van de formules die worden beschreven hier gebruikt u wiskundige en trigonometrische functies zoals rest en TRUNC, Logische functies , zoals alsen Tekstfuncties , zoals OPMAKEN voor meer informatie over andere DAX-functies, Zie het gedeelte Extra bronnen verderop in dit artikel.

Formulevoorbeelden voor een kalenderjaar

De volgende voorbeelden beschrijven formules gebruikt om te maken van extra kolommen in een datumtabel met de naam Calendar. Een kolom met datum, de naam bestaat al en bevat een aaneengesloten bereik van datums vanaf 1/1/2010 tot en met 12/31/2016.

Year

=Year([Date])

In deze formule retourneert de functie jaar het jaar uit de waarde in de kolom datum. Omdat de waarde in de kolom datum van het gegevenstype datetime, weet de functie jaar hoe om terug te keren van het jaar uit.

Jaarkolom

Maand

=Month([Date])

In deze formule, net zoals met de functie jaar gebruiken we de functie maand om te retourneren van een maandwaarde uit de kolom Date.

Maandkolom

Kwartaal

=Int(([Month]+2)/3)

In deze formule gebruiken we de functie INT om de datumwaarde van een als een geheel getal te retourneren. Het argument dat we voor de functie integer opgeeft is de waarde van de kolom met de maand, 2 toevoegen en die vervolgens wordt gedeeld door 3 om toegang te krijgen van onze kwartaal 1 t/m 4.

Kwartaalkolom

De maandnaam van de

=Format([Date],"MMMM")

In deze formule, als u de naam van de maand, gebruiken we de functie FORMAT een numerieke waarde uit de kolom Date converteren naar tekst. We opgeven de kolom datum als het eerste argument, waarna de valutanotatie. willen we onze maandnaam om weer te geven van alle tekens op, zodat we "mmmm" gebruiken. Dit levert het volgende resultaat op:

Kolom met de maandnaam

Als u wilt retourneren de naam van de maand afgekort tot drie letters, we gebruikt u 'mmm' in het notatieargument.

Dag van Week

=Format([Date],"ddd")

In deze formule gebruiken we de functie FORMAT om de dagnaam van de. Omdat we alleen de dagnaam van een afgekorte wilt, geef we 'ddd' in het notatieargument.

Kolom voor dag van de week
Voorbeelddraaitabel

Nadat u de velden voor datums, zoals jaar, kwartaal, maand, enzovoort hebt, kunt u deze kunt gebruiken in een draaitabel of het rapport. Bijvoorbeeld ziet de volgende afbeelding u het veld van de tabel Sales faculteit het verkoopbedrag in waarden, en jaar en kwartaal uit de tabel Calendar dimensie in rijen. Verkoopbedrag wordt voor jaar en kwartaal context samengevoegd.

Voorbeelddraaitabel

Voorbeelden van formules voor een fiscaal jaar

Fiscaal jaar

= IF([Month]<= 6,[Year],[Year]+1)

In dit voorbeeld begint het fiscale jaar op 1 juli.

Er is geen functie die een fiscaal jaar kan worden opgehaald uit een datumwaarde omdat de begin- en einddatums voor een fiscaal jaar vaak verschilt van een kalenderjaar. Als u het fiscale jaar, gebruiken we eerst een functie als om te testen of de waarde voor de maand kleiner dan of gelijk is aan 6 is. In het tweede argument, als de waarde voor maand kleiner dan of gelijk aan 6 is, retourneert de waarde van de kolom Year. Als dat niet zo is, klikt u vervolgens de retourwaarde van jaar en 1 toevoegen.

Kolom met het fiscale jaar

Een andere manier om op te geven van een fiscaal jaar einde maandwaarde is het opzetten van een maateenheid die gewoon de maand geeft. Bijvoorbeeld FYE: = 6. Vervolgens kunt u verwijzen naar de naam van de maateenheid in plaats van het maandnummer. Bijvoorbeeld = IF([Month]<=[FYE],[Year],[Year]+1). Hier vindt u meer flexibiliteit wanneer u verwijst naar de maand voor het einde van fiscaal jaar in aantal verschillende formules.

Fiscale maand

= IF([Month]<= 6, 6+[Month], [Month]-6)

In deze formule Geef we als de waarde voor [maand] is kleiner dan of gelijk is aan 6, maakt u 6 en de waarde van de maand toevoegen, anders aftrekken 6 uit de waarde van [maand].

Kolom met de fiscale maand

Fiscaal kwartaal

=Int(([FiscalMonth]+2)/3)

De formule die we voor FiscalQuarter gebruiken is vergelijkbaar, zoals deze voor kwartaal van onze kalenderjaar was. De enige verschil is dat we [FiscalMonth] opgeven in plaats van [maand].

Kolom voor fiscaal kwartaal

Feestdagen of speciale datums

U kunt een datumkolom ophalen die aangeeft dat bepaalde datums zijn feestdagen of andere speciale datums bevatten. U wilt bijvoorbeeld verkooptotalen voor dag van de nieuwe jaren optellen door een veld Feestdagen toevoegen aan een draaitabel, zoals een slicer of een filter. In andere gevallen wilt u mogelijk uitsluiten die datums uit andere datumkolommen of in een maateenheid.

Inclusief feestdagen of speciale dagen is heel eenvoudig. U kunt een tabel maken in Excel met de datums die u wilt opnemen. U kunt vervolgens kopiëren of gebruik toevoegen aan gegevensmodel toe te voegen aan het gegevensmodel als een gekoppelde tabel. In de meeste gevallen hoeft u geen te maken van een relatie tussen de tabel en de tabel Calendar. Eventuele formules die verwijzen naar deze kunnen gebruik van de functie LOOKUPVALUE om waarden te retourneren.

Hieronder volgt een voorbeeld van een tabel die is gemaakt in Excel feestdagen bevat die moeten worden toegevoegd aan de datumtabel:

Datum

Feestdag

1/1/2010

Nieuwe jaren

11/25/2010

Thanksgiving

25-12-2010

Kerstmis

01-01-2011

Nieuwe jaren

24-11/2011

Thanksgiving

25/12/2011

Kerstmis

1-1-2012

Nieuwe jaren

22.11.12

Thanksgiving

25-12-2012

Kerstmis

1/1/2013

Nieuwe jaren

11/28/2013

Thanksgiving

12/25/2013

Kerstmis

11-27-2014

Thanksgiving

25-12-2014

Kerstmis

1-1-2014

Nieuwe jaren

11-27-2014

Thanksgiving

25-12-2014

Kerstmis

1-1-2015

Nieuwe jaren

26-11-2014

Thanksgiving

25-12-2015

Kerstmis

01.01.16

Nieuwe jaren

24-11/2016

Thanksgiving

12/25/2016

Kerstmis

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

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],Calendar[Date])

Laten we bekijken deze formule nu uitgebreider.

We gebruiken de functie LOOKUPVALUE om waarden uit de kolom Holiday in de tabel Holidays. In het eerste argument Geef we de kolom waarin de resultaatwaarde van onze is. We opgeven de kolom Holiday in de tabel Holidays omdat dit de waarde die wordt als resultaat wilt.

LOOKUPVALUE (feestdagen [feestdagen], Holidays[date],Calendar[date]) =

We Geef het tweede argument, de zoekkolom met de datums die we wilt zoeken. We opgeven de kolom Date in de tabel Holidays , als volgt:

= LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Tot slot opgeven we de kolom in onze agenda -tabel met de datums die we wilt zoeken in de tabel met feestdagen . Dit is natuurlijk de datumkolom in de tabel Calendar .

= LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

De kolom Holiday wordt de naam van de feestdag voor elke rij met een datumwaarde die overeenkomt met een datum in de tabel Holidays geretourneerd.

Tabel met feestdagen

Aangepaste kalender: 13 perioden van vier weken

Sommige organisaties, zoals detailhandel of eten service, rapporteren vaak verschillende perioden, zoals 13 perioden van vier weken. Met een 13 perioden vier weken periode-agenda is elke periode 28 dagen. daarom elke periode bevat vier maandag, dinsdag vier, vier woensdagen, enzovoort. Elke periode bevat het aantal dagen en meestal feestdagen wordt vallen binnen dezelfde periode elk jaar. U kunt kiezen om te beginnen een punt op een dag van de week. Net als met datums in een agenda of het fiscale jaar, kunt u DAX extra om kolommen te maken met aangepaste datums.

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

Week

Deze waarde ontstaat het weeknummer die beginnen met de eerste volledige week van het fiscale jaar. In dit voorbeeld de eerste volledige week begint op zondag, dus de eerste volledige week in het eerste fiscale jaar in de tabel Calendar daadwerkelijk begint op 4-7-2010 en doorloopt op de laatste volledige week in de tabel Calendar. Terwijl deze waarde zelf niet alle handig in de analyse, is het nodig is om te berekenen voor gebruik in andere 28 dagen periode formules.

=Int([Date]-40356)/7)

Laten we bekijken deze formule nu uitgebreider.

Maak eerst een formule waarmee waarden uit de kolom Date worden geretourneerd als geheel als volgt:

=Int([Date])

We vervolgens wilt zoeken naar de eerste zondag in het eerste fiscale jaar. Zien we dat dit is 4-7-2010.

Weekkolom

Nu aftrekken 40356 (dit is het gehele getal voor 6-27/2010, de laatste zondag uit het vorige fiscaal jaar) van die waarde om het aantal dagen sinds het begin van dagen in onze agendatabel, als volgt:

= INT([date]-40356)

Deel het resultaat door 7 (dagen in een week), zoals hier:

=Int(([Date]-40356)/7)

Het resultaat ziet er zo uit:

Weekkolom

termijn

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

=Int(([week]+3)/4)

Laten we bekijken deze formule nu uitgebreider.

Maak eerst een formule waarmee een waarde uit de kolom Week geretourneerd als geheel als volgt:

=INT([Week])

Tel 3 op bij deze waarde als volgt:

= INT([Week]+3)

Deel het resultaat van 4 wordt weergegeven, als volgt:

=Int(([week]+3)/4)

Het resultaat ziet er zo uit:

Periodekolom

Fiscaal jaar voor periode

Deze waarde wordt het fiscale jaar voor een periode.

= INT (([Period]+12)-13) +2008

Laten we bekijken deze formule nu uitgebreider.

Maak eerst een formule die geeft als resultaat een waarde uit Period en waarbij 12 wordt opgeteld:

= ([Periode] + 12)

U deelt het resultaat door 13, omdat er 13 perioden van 28 dagen in het fiscale jaar:

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

Voeg 2010 toe, omdat dit het eerste jaar in de tabel:

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

Ten slotte we de functie INT gebruiken om het verwijderen van een eventuele breuk uit het resultaat en een geheel getal te retourneren na de deling door 13, als volgt:

=INT(([Period]+12)-13)+2010

Het resultaat ziet er zo uit:

Kolom met fiscaal jaar voor periode

Periode in FiscalYear

Deze waarde retourneert de periode getal, 1-13, beginnend vanaf de eerste volledige periode (dat begint op zondag) in elk boekjaar.

= IF(MOD([Period],13), MOD([Period],13),13)

Deze formule is iets meer complexe, zodat we het eerst wordt beschrijven in een taal die we beter te begrijpen. Deze formule staat, deelt de waarde van [periode] door 13 om een periode getal (1-13) in het jaar. Als dat getal 0 is, is het resultaat 13.

Maak eerst een formule die resulteert in de rest van de waarde uit Period door 13. We kunnen de rest (wiskundige en trigonometrische functies) gebruiken als volgt:

=MOD([Period],13)

Hierdoor, voor het grootste deel ontstaat het resultaat dat we horen, tenzij de waarde voor de periode 0 is omdat deze datums niet binnen het eerste fiscale jaar, zoals in de eerste vijf dagen van de datumtabel van onze voorbeeld-agenda vallen. We kunnen gedaan met een functie als. Geval onze resultaat 0 is, retourneren we 13, als volgt:

=Als(MOD([Period],13), rest ([periode], 13), 13)

Het resultaat ziet er zo uit:

Kolom met periode in fiscaal jaar

Voorbeelddraaitabel

De onderstaande afbeelding ziet u een draaitabel met de verkoopbedrag veld uit de tabel Sales faculteit in waarden en PeriodFiscalYear en PeriodInFiscalYear velden uit de tabel Calendar datum dimensie in rijen. Verkoopbedrag wordt door het fiscale jaar en 28 dagen in het fiscale jaar voor de context samengevoegd.

Voorbeelddraaitabel voor fiscaal jaar

Relaties

Nadat u een datumtabel hebt gemaakt in uw gegevensmodel, om te beginnen met zoeken van uw gegevens in draaitabellen en rapporten en gegevens aggregeren op basis van de kolommen in uw tabel van de dimensie datum moet u een relatie maken tussen de feitentabel met uw transactiegegevens en de datumtabel.

Omdat u nodig hebt om te maken van een relatie op basis van datums, zult u om ervoor te zorgen dat u maakt die relatie tussen kolommen waarvan de waarden van het gegevenstype datetime (datum zijn).

Voor elke datumwaarde in de feitentabel, moet de gerelateerde opzoekkolom in de datumtabel overeenkomende waarden bevatten. Bijvoorbeeld een rij (transactierecord) in de tabel Sales faculteit met een waarde van 8-15-2012 12:00 AM in de kolom DateKey een overeenkomstige waarde in de gerelateerde kolom datum in de tabel date (benoemde agenda) moet hebben. Dit is een van de belangrijkste redenen dat u wilt dat uw datumkolom in de tabel date naar een aaneengesloten bereik van datums met een bepaalde datum mogelijke in uw feitentabel bevatten.

Relaties in de diagramweergave

Opmerking: Hoewel de datumkolom in elke tabel moet hetzelfde gegevenstype (datum), belangrijk de opmaak van elke kolom niet...

Opmerking: Als Power Pivot u relaties maken tussen de twee tabellen kunt, worden mogelijk niet de datumvelden opgeslagen de datum en tijd op hetzelfde niveau van de precisie van formules. Afhankelijk van de kolomopmaak, de waarden hetzelfde kunnen er uitzien, maar anders worden opgeslagen. Lees meer over het werken met tijd.

Opmerking: Vermijd het gebruik van integersurrogaatsleutels in relaties. Wanneer u gegevens uit een relationele gegevensbron importeren, vaak datum en tijd-kolommen worden aangegeven door een vervangende sleutel, dat wil zeggen een geheel getal kolom waarmee een unieke datum voorstelt. In Power Pivot, moet u relaties maken met behulp van de gehele getal datum/tijd toetsen voorkomen en kolommen die unieke waarden met het gegevenstype van een datum bevatten in plaats daarvan, gebruiken. Hoewel het gebruik van de vervangende toetsen een aanbevolen in traditionele datawarehouses wordt, wordt de toetsen van de gehele getal niet nodig zijn in Power Pivot en kunnen moeilijker groep waarden in draaitabellen door verschillende perioden.

Als er een gegevenstypen niet overeenkomen wanneer u probeert een relatie maken, is het waarschijnlijk omdat de kolom in de feitentabel niet van het gegevenstype datum is. Dit kan gebeuren wanneer de Power Pivot kan niet automatisch een niet-datum (meestal een tekstgegevenstype) worden geconverteerd naar het datumgegevenstype. U kunt nog steeds de kolom in uw feitentabel, maar u moet de gegevens met een DAX-formule in een nieuwe berekende kolom converteren. Zie converteren tekstgegevens Typ datums naar het datumgegevenstype verderop in de bijlage.

Meerdere relaties

In sommige gevallen, is het mogelijk dat deze moet u meerdere relaties maken of meerdere datumtabellen maken. Bijvoorbeeld als er meerdere datumvelden in de tabel Sales faculteit, zoals DateKey, shipdate telt en ReturnDate, kunnen ze alle relaties naar het veld datum in de tabel Calendar datum, maar alleen een van deze kan zijn een actieve relatie. In dit geval DateKey staat voor de datum van de transactie en kunnen daarom de belangrijkste datum, dit zou aanbevolen gebruikt als de actieve relatie. De andere hebben inactieve relaties.

De volgende draaitabel berekent totale verkoop per fiscaal jaar en fiscaal kwartaal. Een maateenheid voor de naam van totale verkoop, met de formule Totale Sales:=SUM([SalesAmount]), wordt in waarden en FiscalYear geplaatst en FiscalQuarter velden uit de tabel Calendar-datum in rijen worden geplaatst.

Draaitabel voor totale verkoop per fiscaal kwartaal Lijst met draaitabelvelden

Deze eenvoudige draaitabel werkt correct omdat we wilt optellen van onze totale verkoop door de transactie-datum in DateKey. De maateenheid van onze totale verkoop worden de datums in DateKey en door het fiscale jaar en fiscaal kwartaal wordt opgeteld omdat er een relatie tussen DateKey in de tabel Sales en de kolom Date in de tabel Calendar datum.

Inactieve relaties

Maar wat als we wilt optellen onze totale verkoop niet door transactiedatum, maar dan met verzenddatum? We nodig een relatie tussen de kolom shipdate telt in de tabel Sales en de kolom Date in de tabel Calendar. Als er geen die relatie maakt, worden altijd onze aggregaties gebaseerd op de transactiedatum. We kunnen wel meerdere relaties, zelfs als er kan slechts één actief zijn, en omdat transactiedatum de belangrijkste is, deze de actieve relatie met de tabel Calendar krijgt.

In dit geval heeft shipdate telt een niet-actieve relatie, zodat een maateenheid formule naar gegevens aggregeren op basis van leveringsdatums gemaakt de niet-actieve relatie opgeven moet met behulp van de functie USERELATIONSHIP .

Omdat er een niet-actieve relatie tussen de kolom shipdate telt in de tabel Sales en de kolom Date in de tabel Calendar, kunnen we bijvoorbeeld een maateenheid die de som van totale verkoop per verzenddatum maken. We een formule zoals deze gebruiken om op te geven van de relatie te gebruiken:

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

Deze formule gewoon aanwezigheidsstatussen: berekenen van een som voor verkoopbedrag, maar filteren met behulp van de relatie tussen de kolom shipdate telt in de tabel Sales en de kolom Date in de tabel Calendar.

Als we een draaitabel maken en totale verkoop per eenheid datum verzenden in waarden, en fiscaal jaar en fiscaal kwartaal op rijen hebt opgeslagen, zien we het dezelfde eindtotaal, maar alle andere som bedragen voor fiscaal jaar en fiscaal kwartaal zijn verschillende omdat ze zijn gebaseerd op de verzenddatum en niet de transactiedatum.

Draaitabel voor totale verkoop per verzenddatum Lijst met draaitabelvelden

Inactieve relaties gebruikt, kunt u slechts één datumtabel te gebruiken, maar deze is vereist dat alle maateenheden (zoals totale verkoop per verzenddatum), verwijzen naar de niet-actieve relatie in de formule. Er is nog een alternatief, dat wil zeggen, meerdere datumtabellen gebruiken.

Meerdere datumtabellen

Een andere manier om te werken met meerdere datumkolommen in uw feitentabel is voor het maken van meerdere datumtabellen en afzonderlijke actieve relaties hiertussen maken. Laten we onze verkoop tabel voorbeeld opnieuw. We hebben drie kolommen met datums die wij willen statistische gegevens op:

  • Een DateKey met de verkoopdatum voor elke transactie.

  • Een ShipDate 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 is ontvangen.

Onthoud dat het veld DateKey met de transactiedatum belangrijkste is. We doet onze aggregaties op basis van deze datums, zodat we een relatie tussen deze en de kolom Date in de tabel Calendar ook later meestal. Als we niet wilt inactief om relaties te maken tussen shipdate telt en ReturnDate en het veld Date in de tabel Calendar, kunt dus het vereisen van speciale maateenheid formules, we extra datumtabellen maken voor verzenddatum en afzender datum. We kunnen actieve relaties hiertussen maken.

Relaties met meerdere datumtabellen in de diagramweergave

In dit voorbeeld hebt we een andere datumtabel met de naam ShipCalendar gemaakt. Dit doet natuurlijk ook betekent extra datumkolommen maken en omdat deze datumkolommen in een andere datum-tabel, wordt deze manier om het verschil dezelfde kolommen in de tabel Calendar met naam wilt geven. Bijvoorbeeld hebt die u hebt gemaakt met de naam ShipYear, ShipMonth ShipQuarter en dergelijke kolommen.

Als we onze draaitabel maken en de maateenheid van onze totale verkoop in waarden, en ShipFiscalYear en ShipFiscalQuarter in rijen wilt plaatsen, zien we hetzelfde resultaat als we een niet-actieve relatie en een speciale totale verkoop per verzenddatum berekend veld gemaakt hebt gezien.

Draaitabel voor totale verkoop per verzenddatum met de verzendingskalender Lijst met draaitabelvelden

Elk van de volgende manieren is dat u daarbij aandacht vereist. Als u meerdere relaties met een tabel met één datum, is het wellicht speciale maatregelen die douanevervoer inactieve relaties met behulp van de functie USERELATIONSHIP maken. Aan de andere kant maken van meerdere datumtabellen kan verwarrend in een lijst met velden en omdat er meer tabellen in het gegevensmodel, meer geheugen vereist. Experimenteren met wat het meest geschikt is voor u.

Eigenschap datumtabel

De eigenschap Datumtabel stelt de metagegevens nodig voor Time Intelligence-functies zoals TOTALYTD, PREVIOUSMONTH en DATESBETWEEN goed. Wanneer een berekening wordt uitgevoerd met een van deze functies, weet van Power Pivot formule-engine waar kunt u voor de datums die nodig zijn.

Waarschuwing: Als deze eigenschap niet is ingesteld, mogelijk metingen met DAX Time Intelligence-functies niet juiste resultaten geretourneerd.

Wanneer u de eigenschap Datumtabel instelt, geeft u een datumtabel en een datumkolom met het gegevenstype datum (datetime) in deze.

Dialoogvenster Als datumtabel markeren

Hoe u: Stel de eigenschap Datumtabel

  1. Selecteer de tabel Calendar in het PowerPivot-venster.

  2. Klik op het tabblad ontwerp en klikt u op als datumtabel markeren.

  3. Selecteer in het markeren als in het dialoogvenster Datumtabel, een kolom met unieke waarden en het gegevenstype datum.

Werken met tijd

Alle datumwaarden met het gegevenstype van een datum in de Excel- of SQL Server zijn daadwerkelijk een getal. Opgenomen in dat getal zijn cijfers die verwijzen naar een tijd. In veel gevallen is dat moment voor elke rij middernacht. Als een DateTimeKey-veld in een feitentabel verkoop bevat bijvoorbeeld waarden zoals 10/19/2010 12:00:00 AM, dit betekent dat de waarden de mogelijkheden van de dag van de precisie van formules zijn. Als de veldwaarden DateTimeKey een tijd die is opgenomen, bijvoorbeeld 10/19/2010 hebt 8:44:00 AM, dit betekent dat de waarden de mogelijkheden van de minuut van precisie van formules zijn. Waarden kunnen ook worden naar uur niveau precisie van formules, of zelfs seconden niveau van de precisie van formules. Het niveau van de precisie van formules in de tijdwaarde heeft een belangrijke invloed op hoe u de datumtabel en de relaties tussen deze en uw feitentabel maken.

U moet bepalen als u uw gegevens naar een niveau van de dag van de precisie van formules of naar een niveau van de precisie van formules aggregeert. Mogelijk wilt u met andere woorden, kolommen gebruiken in uw datumtabel zoals ochtend, namiddag of uur datumvelden tijd in een draaitabel van rij, kolom of hierop wilt filteren gebieden.

Opmerking: Dagen zijn het kleinste element van de tijd die DAX Time Intelligence-functies met werken kunnen. Als u niet nodig hebt om te werken met tijdwaarden, moet u de precisie van uw gegevens op het gebruiken van dagen als de minimale eenheid verkleinen.

Als u van plan bent om samen te voegen van uw gegevens om het tijdsniveau van de, moet de datumtabel een datumkolom ophalen met de tijd die is opgenomen. En zelfs essentieel, deze wordt een datumkolom met één rij nodig voor elk uur, of vanaf elke minuut, van elke dag voor elk jaar in het datumbereik. Dit is omdat, als u wilt een relatie tussen de kolom DateTimeKey in de feitentabel en de kolom date in de datumtabel maakt, moet u overeenkomstige waarden. Zoals u zich voorstellen kunt, als u een groot aantal jaren, kunt zo u voor een zeer grote datumtabel.

In de meeste gevallen echter wilt u uw gegevens alleen voor de dag. Met andere woorden, wordt u kolommen zoals jaar, maand, Week of dag van Week gebruiken als velden in een draaitabel van rij, kolom of gebieden filteren. In dit geval hoeft de kolom date in de tabel date slechts één rij bevatten voor elke dag in een jaar, zoals we eerder is beschreven.

Als uw datumkolom een niveau van de precisie van formules bevat, maar u alleen op het niveau van een dag aggregeert, maken de relatie tussen de feitentabel en de datumtabel mogelijk moet u uw feitentabel wijzigen door het maken van een nieuwe kolom die de waarden in de datum-c afgekapt olom naar een dagwaarde. Met andere woorden, converteren van een waarde is zoals 19-10-2010 8:44:00AM naar 10/19/2010 12:00:00 AM. U kunt vervolgens de relatie tussen deze nieuwe kolom en de datumkolom maken in de datumtabel, omdat de waarden overeenkomen.

Laten we even naar een voorbeeld kijken. Deze afbeelding ziet een kolom DateTimeKey in de tabel Sales faculteit. Alle van de aggregaties voor de gegevens in deze tabel hoeft alleen te worden naar de dag niveau, met behulp van kolommen in de tabel Calendar datum zoals jaar, maand, kwartaal, enzovoort. De tijd die is opgenomen in de waarde is niet relevant, alleen de werkelijke datum.

Kolom DateTimeKey

Omdat we niet nodig hebt om deze gegevens de mogelijkheden van de tijd te analyseren, nodig niet we de kolom datum in de tabel Calendar datum één rij opnemen voor elk uur en elke minuut van elke dag in elk jaar. Ja, de datumkolom in onze datumtabel ziet er zo uit:

Datumkolom in Power Pivot

Als u wilt maken van een relatie tussen de kolom DateTimeKey in de tabel Sales en de kolom Date in de tabel Calendar, kunnen we maken van een nieuwe berekende kolom in de tabel Sales faculteit en de geheel -functie gebruiken om de datum en tijd-waarde in de DateTimeKey afkappen de kolom in een datumwaarde die overeenkomt met de waarden in de kolom Date in de tabel Calendar. Onze formule ziet er zo uit:

=TRUNC([DateTimeKey],0)

Hierdoor ontstaat een nieuwe kolom (die we datekey) met de datum van de kolom DateTimeKey en de tijd 12:00:00 AM voor elke rij:

Kolom DateKey

Nu kunnen we een relatie tussen deze nieuwe kolom (DateKey) en de kolom Date in de tabel Calendar maken.

Daarnaast kunt we een berekende kolom maken in de tabel Sales die Hiermee reduceert u de precisie van de tijd in de kolom DateTimeKey op uurniveau van precisie van formules. In dit geval de functie geheel niet werkt, maar we kunnen andere DAX-datum- en tijdfuncties nog steeds gebruiken om te extraheren en opnieuw samenvoegen een nieuwe waarde naar een uurniveau van de precisie van formules. We kunt een formule zoals deze gebruiken:

= DATUM (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey])) + tijd (HOUR([DateTimeKey]), 0, 0)

De nieuwe kolom ziet er zo uit:

Kolom DateTimeKey

Als de kolom Date in de datumtabel waarden bevat op uurniveau van precisie van formules, kunnen we vervolgens een relatie hiertussen maken.

Datums eenvoudiger

Veel van de datumkolommen die u in de datumtabel maakt nodig zijn voor andere velden, maar echt zijn niet alle handig in de analyse. Het veld DateKey in de tabel Sales we hebt waarnaar wordt verwezen en weergegeven in dit artikel is bijvoorbeeld belangrijk omdat voor elke transactie, die transactie is geregistreerd als een bepaalde datum en tijd op. Maar uit een analyse en rapportage van oogpunt, is het niet alle nuttig omdat we deze niet als een rij, kolom of filterveld in een draaitabel of het rapport gebruiken.

Op dezelfde manier in ons voorbeeld de kolom Date in de tabel Calendar is heel handig, en zelfs essentieel, maar u kunt deze niet gebruiken als dimensie in een draaitabel.

Tabellen en kolommen om rekening te houden ze zo bruikbaar mogelijk, en om draaitabel- of Power View-rapport die veld gemakkelijker bevat om te navigeren, is het is belangrijk om te verbergen onnodige kolommen voor clienthulpprogramma's. U kunt ook bepaalde tabellen ook verbergen. De tabel Holidays eerder weergegeven bevat feestdagen datums die belangrijk voor bepaalde kolommen in de tabel Calendar, maar u de datum niet gebruiken en feestdagen kolommen in de feestdagen tabel zichzelf als velden in een draaitabel zijn. Hier nogmaals kunt om veld bevat gemakkelijker maken om te navigeren, u verbergen de hele tabel met feestdagen.

Een andere belangrijke aspecten van het werken met datums is naamgevingsconventies. U kunt naam toewijzen aan tabellen en kolommen in Power Pivot elke gewenste. Maar in gedachten houden, vooral als u uw werkmap wilt delen met andere gebruikers, een goede naamgevingsconventie vergemakkelijkt het identificeren van tabellen en datums, niet alleen in het veld bevat, maar ook in Power Pivot en in DAX-formules.

Nadat u een datumtabel in uw gegevensmodel hebt, kunt u beginnen met het maken van eenheden waarmee u optimaal gebruikmaken van uw gegevens. Een deel mogelijk net zo eenvoudig als het verkooptotalen voor het huidige jaar optellen en anderen mogelijk meer complexe, waar u wilt filteren op een bepaald bereik met unieke datums. Meer informatie in metingen in Power Pivot en Time Intelligence-functies.

Bijlage

Tekstgegevenstype converteren datums naar het datumgegevenstype

In sommige gevallen, kan een feitentabel met transactiegegevens datums gegevenstype tekst bevatten. Dat wil zeggen een datum die wordt weergegeven als 2012-12-04T11:47:09 in feite een datum helemaal is of ten minste niet het type datum Power Pivot kunnen begrijpen. Het echt alleen tekst zoals een datum. Een relatie maken tussen een datumkolom in de feitentabel en een datumkolom ophalen in een datumtabel, zijn beide kolommen van het gegevenstype datum .

Meestal wanneer u probeert te wijzigen van het gegevenstype voor een kolom met datums die zijn van het tekstgegevenstype naar een datumgegevenstype, Power Pivot kunt interpreteren van de datums en automatisch converteren naar een echt gegevenstype date. Als de Power Pivot niet mogelijk een gegevenstypeconversie, krijgt u een gegevenstypen niet overeenkomen.

U kunt de datums echter nog steeds converteren naar een echt gegevenstype date. U kunt een nieuwe berekende kolom maken en een DAX-formule gebruiken om te parseren van het jaar, maand, dag, tijd, enzovoort uit de tekenreeksen en daarna samengevoegd deze weer samen op een manier die Power Pivot als een waar datum lezen kunnen.

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

DateTime-kolom in een feitentabel

Als we naar gegevenstype in het tabblad Start opmaak groep Power Pivot van kijken, zien we dat het gegevenstype tekst is.

Gegevenstype op het lint

We kan een relatie tussen de DateTime-kolom en de kolom Date in onze datumtabel maken omdat de gegevenstypen niet overeenkomen. Als we probeert te wijzigen van het gegevenstype datum, krijgen we een gegevenstypen niet overeenkomen:

Typeconflict

In dit geval is Power Pivot niet het gegevenstype van tekst converteren naar datums. We kunnen deze kolom nog steeds gebruiken, maar om te kunnen werken in een gegevenstype waar datum, moeten we een nieuwe kolom die de tekst parseert en opnieuw gemaakt in een waarde die Power Pivot het datumgegevenstype aanbrengen kunt maken.

Denk eraan dat het werkt met gedeelte eerder in dit artikel. tenzij het nodig zijn om uw analyse te een niveau tijdgegevens-precisie van is, moet u datums converteren in uw feitentabel naar een niveau van de dag van de precisie van formules. Met die in gedachten, we horen de waarden in de nieuwe kolom worden op het niveau van de dag van de precisie van formules (met uitzondering van tijd). We kunnen zowel de waarden in de kolom datum-/ converteren naar een datumgegevenstype en de tijd inspringniveau precisie van formules met de volgende formule verwijderen:

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

Hierdoor ontstaat een nieuwe kolom (in dit geval met de naam datum). PowerPivot zelfs worden de waarden om datums worden gedetecteerd en het gegevenstype automatisch worden ingesteld op datum.

Kolom Date in feitentabel

Als u wilt behouden de precisie van formules, breiden we gewoon de formule als u wilt de uren, minuten en seconden opnemen uit.

= 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 met het datumgegevenstype hebben, kunnen we een relatie tussen deze en een datumkolom ophalen maken in een datum.

Aanvullende bronnen

Datums in Power Pivot

Berekeningen in Power Pivot

QuickStart: informatie over de grondbeginselen van DAX in 30 minuten

Naslag voor Data Analysis Expressions

DAX-resources

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×