Een datumtabel maken
Tijdens het maken van een rapport in Power BI is het een algemene bedrijfsvereiste om berekeningen te maken op basis van datum en tijd. Organisaties willen weten hoe hun bedrijf het doet in de loop van de maanden, kwartalen, boekjaren, enzovoort. Daarom is het van cruciaal belang dat deze tijdgebonden waarden correct zijn ingedeeld. Power BI detecteert automatisch op datumkolommen en -tabellen. Er kunnen echter situaties optreden waarin u extra stappen moet maken om de datums in de indeling te krijgen die voor uw organisatie nodig is.
Stel bijvoorbeeld dat u rapporten ontwikkelt voor het verkoopteam in uw organisatie. De database bevat tabellen voor verkoop, orders, producten en meer. U ziet dat veel van deze tabellen, inclusief Sales en Orders, eigen datumkolommen bevatten, zoals wordt weergegeven in de kolommen ShipDate en OrderDate in de tabellen Sales en Orders. U krijgt de opdracht om een tabel te ontwikkelen met de totale verkopen en orders per jaar en maand. Hoe kunt u een visual maken met meerdere tabellen, die allemaal verwijzen naar hun eigen datumkolommen?
Voor de oplossing van dit probleem kunt u een gemeenschappelijke datumtabel maken die kan worden gebruikt voor meerdere tabellen. In de volgende sectie wordt uitgelegd hoe u deze taak kunt uitvoeren in Power BI.
Een gemeenschappelijke datumtabel maken
Manieren waarop u een gemeenschappelijke datumtabel kunt maken, zijn:
Brongegevens
DAX
Power Query
Brongegevens
In sommige gevallen hebben de brondatabases en -datawarehouses al hun eigen datumtabellen. Als de beheerder die de database heeft ontworpen, zijn werk grondig heeft gedaan, kunnen deze tabellen worden gebruikt om de volgende taken uit te voeren:
Bedrijfsvakanties bepalen
Afzonderlijk kalender- en boekjaar
Weekends van werkdagen onderscheiden
Brongegevenstabellen zijn goed uitgewerkt en direct gebruiksklaar. Als u een tabel als zodanig hebt, moet u deze opnemen in uw semantische model en geen andere methoden gebruiken die in deze sectie worden beschreven. Het wordt aanbevolen om een brongegevenstabel te gebruiken, omdat deze waarschijnlijk wordt gedeeld met andere hulpprogramma's die u mogelijk gebruikt naast Power BI.
Als u geen brongegevenstabel hebt, kunt u andere manieren gebruiken om een gemeenschappelijke datumtabel samen te stellen.
DAX
U kunt de DAX-functies (Data Analysis Expression) CALENDARAUTO() of CALENDAR() gebruiken om uw gemeenschappelijke datumtabel samen te stellen. Met de functie CALENDAR() wordt een aaneengesloten datumbereik geretourneerd op basis van een begin- en einddatum die als argumenten in de functie zijn ingevoerd. De functie CALENDARAUTO() retourneert ook een aaneengesloten, volledig datumbereik dat automatisch wordt bepaald op basis van uw semantische model. De begindatum wordt gekozen als de vroegste datum in uw semantische model en de einddatum is de laatste datum in uw semantische model plus gegevens die zijn ingevuld tot de fiscale maand die u kunt opnemen als een argument in de functie CALENDARAUTO(). Voor dit voorbeeld wordt de functie CALENDAR() gebruikt omdat u alleen de gegevens van 31 mei 2011 (de eerste dag waarop de afdeling Verkoop deze gegevens is begonnen bij te houden) en daarna gedurende de volgende 10 jaar wilt zien.
Selecteer nieuwe tabel in Power BI Desktop en voer de volgende DAX-formule in:
Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))
U hebt nu een kolom met datums die u kunt gebruiken. Deze kolom is echter een vrij eenvoudig. U wilt ook kolommen bekijken voor uitsluitend het jaar, het maandnummer, de week van het jaar en de dag van de week. U kunt deze taak uitvoeren door Nieuwe kolom op het lint te selecteren en de volgende DAX-vergelijking in te voeren, waardoor het jaar uit uw datumtabel wordt opgehaald.
Year = YEAR(Dates[Date])
U kunt hetzelfde proces uitvoeren om het maandnummer, weeknummer en dag van de week op te halen:
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")
Wanneer u klaar bent, bevat uw tabel de kolommen die in de volgende afbeelding worden weergegeven.
U hebt nu een gemeenschappelijke datumtabel gemaakt met behulp van DAX. Met dit proces wordt alleen de nieuwe tabel toegevoegd aan het semantische model; u moet nog steeds relaties tot stand brengen tussen de datumtabel en de tabellen Verkoop en Order en de tabel vervolgens markeren als de officiële datumtabel van uw semantische model. Voordat u deze taken voltooit, moet u echter nadenken over een andere manier voor het samenstellen van een gemeenschappelijke datumtabel: met behulp van Power Query.
Power Query
U kunt de M-taal, de ontwikkelingstaal die wordt gebruikt voor het maken van query's in Power Query, gebruiken om een gemeenschappelijke datumtabel te definiëren.
Selecteer Gegevens transformeren in Power BI Desktop, waarmee u naar Power Query wordt geleid. Klik in de lege ruimte van het linkerdeelvenster Query's met de rechtermuisknop om de volgende vervolgkeuzelijst te openen, waar u Nieuwe query lege query >selecteert.
Voer in de resulterende weergave Nieuwe query de volgende M-formule in om een kalendertabel te maken:
= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
Voor uw verkoopgegevens wilt u dat de begindatum overeenkomt met de vroegste datum die u in uw gegevens hebt: 31 mei 2011. Daarnaast wilt u de datums bekijken voor de komende tien jaar, inclusief datums in de toekomst. Op deze manier zorgt u ervoor dat u met de instroom van nieuwe verkoopgegevens deze tabel niet opnieuw hoeft te maken. U kunt ook de duur wijzigen. In dit geval wilt u een gegevenspunt voor elke dag, maar u kunt deze ook verhogen per uur, minuut en seconde. In de volgende afbeelding wordt het resultaat getoond.
Nadat u het proces hebt gerealiseerd, ziet u dat u een lijst met datums hebt in plaats van een tabel met datums. Als u deze fout wilt corrigeren, gaat u naar het tabblad Transformeren op het lint en selecteert u Converteren > naar tabel. Zoals de naam al zegt, wordt de lijst met deze functie geconverteerd naar een tabel. U kunt ook de naam van de kolom wijzigen in DateCol.
Vervolgens wilt u kolommen toevoegen aan de nieuwe tabel om de datums in termen van jaar, maand, week en dag weer te geven, zodat u een hiërarchie in uw visual kunt inbouwen. Uw eerste taak is het kolomtype te wijzigen door het pictogram naast de naam van de kolom te selecteren en in de resulterende vervolgkeuzelijst het type Datumte selecteren.
Wanneer u klaar bent met het selecteren van het type Datum, kunt u kolommen toevoegen voor jaar, maanden, weken en dagen. Ga naar Kolom toevoegen, selecteer de vervolgkeuzemenu onder Datum en selecteer vervolgens Jaar, zoals wordt weergegeven in de volgende afbeelding.
U ziet dat Power BI een kolom van alle jaren heeft toegevoegd die uit DateCol zijn opgehaald.
Voer hetzelfde proces uit voor maanden, weken en dagen. Wanneer u dit proces hebt afgerond, bevat uw tabel de kolommen die in de volgende afbeelding worden weergegeven.
U hebt nu Power Query gebruikt voor het maken van een gemeenschappelijke datumtabel.
In de vorige stappen ziet u hoe u de tabel in het semantische model kunt krijgen. U moet uw tabel nu markeren als de officiële datumtabel, zodat Power BI deze kan herkennen voor alle toekomstige waarden en kan controleren of de opmaak juist is.
Als officiële datumtabel markeren
Uw eerste taak in het markeren van de tabel als officiële datumtabel is het vinden van de nieuwe tabel in het deelvenster Velden. Klik met de rechtermuisknop op de naam van de tabel en selecteer daarna Markeren als datumtabel, zoals te zien is in de volgende afbeelding.
Door de tabel als een datumtabel te markeren, voert Power BI validaties uit om ervoor te zorgen dat de gegevens geen null-waarden bevatten, uniek zijn en doorlopende datumwaarden bevatten gedurende een periode. U kunt ook specifieke kolommen in de tabel selecteren om de datum te markeren. Dit kan handig zijn wanneer uw tabel veel kolommen bevat. Klik met de rechtermuisknop op de tabel, selecteer Markeren als datumtabel en selecteer vervolgens Instellingen voor datumtabel. Het volgende venster wordt weergegeven, waarin u kunt kiezen welke kolom moet worden gemarkeerd als datum.
Als u Markeren als datumtabel selecteert, worden automatisch gegenereerde hiërarchieën verwijderd uit het veld Datum in de tabel die u als een datumtabel hebt gemarkeerd. Voor andere datumvelden is de automatische hiërarchie nog steeds aanwezig totdat u een relatie tot stand brengt tussen dat veld en de datumtabel of totdat u de functie Automatische datum/tijd uitschakelt. U kunt handmatig een hiërarchie toevoegen aan de gemeenschappelijke datumtabel door met de rechtermuisknop te klikken op de kolommen jaar, maand, week of dag in het deelvenster Velden en vervolgens Nieuwe hiërarchie te selecteren. Dit proces wordt verderop in deze module besproken.
Uw visual samenstellen
Als u een visual wilt samenstellen met de tabellen Sales en Orders, moet u een relatie tot stand brengen tussen deze nieuwe gemeenschappelijke datumtabel en de tabellen Sales en Orders. Als gevolg hiervan kunt u visuals maken met behulp van de nieuwe datumtabel. Als u deze taak wilt uitvoeren, gaat u naar het tabblad Model>Relaties beheren, waar u relaties kunt maken tussen de gemeenschappelijke datumtabel en de tabellen Sales en Orders met behulp van de kolom OrderDate. In de volgende schermopname ziet u een voorbeeld van één dergelijke relatie.
Nadat u de relaties hebt gemaakt, kunt u uw visual Totale verkoop- en orderhoeveelheid in de tijd samenstellen met de gemeenschappelijke datumtabel die u hebt ontwikkeld met behulp van de DAX- of Power Query-methode.
Als u de totale verkoop wilt bepalen, moet u alle verkopen toevoegen, omdat de kolom Amount in de tabel Sales alleen de omzet van elke verkoop laat zien, niet de totale omzet uit verkopen. U kunt deze taak voltooien met behulp van de volgende berekening voor de meting, die wordt uitgelegd in latere discussies. De berekening die u gaat gebruiken bij het compileren van deze meting is als volgt:
#Total Sales = SUM(Sales[‘Amount’])
Wanneer u klaar bent, kunt u een tabel maken door terug te gaan naar het tabblad Visualisaties en door de visual Tabel te selecteren. U wilt de totale orders en verkoop per jaar en maand bekijken, dus wilt u alleen de kolommen Year en Month van uw datumtabel, de kolom OrderQty en de meting #TotalSales erin opnemen. Als u meer over hiërarchieën weet, kunt u ook een hiërarchie bouwen waarmee u van jaren tot maanden inzoomt. In dit voorbeeld kunt u ze naast elkaar weergeven. U hebt nu een visual gemaakt met een gemeenschappelijke datumtabel.
