LuckyTemplates-tip: eerste N werkdagen vergelijken

Voor dit bericht wil ik het hebben over een recente LuckyTemplates-tip die ik bedacht voor een lid van het . De vraag behandelde een nuttig patroon in termen van kijken naar de eerste N werkdagen of eerste N factureerbare dagen van een bepaalde maand versus dezelfde periode in de voorgaande maand. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

TJ Henneman wilde de eerste 5 factureerbare dagen van de huidige maand vergelijken met de eerste 5 factureerbare dagen van de vorige maand, en dan op Dag 10, Dag 15 en Dag 20. Merk op dat hij alleen naar de factureerbare dagen wil kijken, weekenden en feestdagen vallen hier dus niet onder. bekijken .

Ik denk dat hier een heel interessante power query-oplossing te vinden is. Ik heb ook met een interessante DAX-oplossing aan dit probleem gewerkt. Ik zal dit laatste in een andere tutorial bespreken.

Ik wil u ook een recente rapportagetechniek laten zien die ik heb gebruikt voor LuckyTemplates Challenge #16. Ik heb een scrollende KPI- indicator gebruikt die heel goed zal werken voor dit specifieke probleem.

Maar voor deze LuckyTemplates-tip ga ik in op de Power Query-oplossing. Laten we naar LuckyTemplates springen en onze gegevens bekijken. Voor deze heb ik de gegevens gesimuleerd met behulp van de oefendataset-tool die we hebben samengesteld bij LuckyTemplates, die je gratis kunt gebruiken. Dit is geweldig voor het samenstellen van een basisgegevensset met sterrenschema's en voor het testen van oplossingen.

Inhoudsopgave

• Ons datamodel
• Een dubbele tabel maken
• De datumtabel samenvoegen
• Een aangepaste kolom maken
• Een What-If-parameter creëren
• Een maat creëren voor het aantal werkdagen
• Conclusie

Ons datamodel

Ons datamodel maakt gebruik van onze uitgebreide datumtabel plus een reeks dimensietabellen. We hebben ook een verkooptabel met drie jaar gedeeltelijke gegevens in het eerste jaar, volledige gegevens in het tweede jaar en gedeeltelijke gegevens in het derde jaar. Als laatste hebben we ook een tabel Maatregelen .

Ik heb deze dataset ook enigszins aangepast. Ik heb een vakantietabel ingevoerd en deze gekoppeld aan de uitgebreide datumtabel , zodat deze de feestdagen die we in deze analyse zullen verwijderen, correct kan berekenen. Als je niet zeker weet hoe je dit moet doen, bekijk dan deze tutorial .

Vervolgens filterde ik de eerste gedeeltelijke maand uit de dataset omdat die maand maar een paar dagen aan data bevatte en niet de werkdagen die we nodig hebben. Houd er rekening mee dat we beginnen met de eerste volledige maand; maar verder is dit uw standaard dataset. Dus laten we naar Power Query springen en aan deze LuckyTemplates-tip gaan werken.

Als we naar onze gegevens kijken, zie je dat we op een bepaalde dag verschillende bestellingen hebben. Het doel is om alles te nummeren dat begint met 1 april als onze eerste werkdag, 2 april als onze tweede werkdag, 3 april als onze derde werkdag, enzovoort. We nummeren dit van 1 tot N werkdagen voor elke maand, terwijl we de weekenden en feestdagen buiten beschouwing laten.

Een dubbele tabel maken

Om te beginnen moeten we een duplicaat van deze tabel maken, zodat we kunnen aggregeren tot Totale verkoop en deze naar het individuele observatieniveau kunnen brengen. We gaan deze duplicaattabel Sales Aggregated noemen .

Het is je misschien opgevallen dat we de tabel Verkoop hebben gedupliceerd in plaats van er alleen maar naar te verwijzen. In dit geval gaan we deze tabel samenvoegen met de originele Sales-tabel. Als we ernaar verwijzen, zal het problemen veroorzaken wanneer we de twee samenvoegen.

Het eerste dat u moet doen, is een Group By gebruiken in de OrderDate-kolom . Klik op de knop Geavanceerd , want dat geeft ons een volledig beeld van wat we hier doen. We zullen Total Day Sales als onze nieuwe kolomnaam gebruiken en het zal de som zijn van ons regeltotaal.

Nu hebben we elke datum (eerste kolom) en de totale verkoop voor die dag (tweede kolom).

De datumtabel samenvoegen

De volgende stap in deze LuckyTemplates-tip is om dit samen te voegen met onze tabel Datums om erachter te komen welke van die dagen werkdagen zijn en welke niet. We kunnen weer groeperen op , die uitsplitsen en op de juiste manier nummeren.

We gaan onze Dates-tabel samenvoegen en de OrderDate aan Date koppelen . We kunnen onderaan zien dat de selectie overeenkomt met elke rij van de tabel, zoals het hoort.

Het veld waarin we vooral geïnteresseerd zijn, is IsBusinessDay .

Dit is het primaire veld waarop we zullen groeperen en gebruiken vervolgens de velden Month & Year en MonthnYear .

We moeten ook DayofWeekName controleren om er zeker van te zijn dat we de juiste dagen eruit filteren.

Zodra we op de knop OK klikken , geeft het ons precies wat we nodig hebben.

We moeten er ook voor zorgen dat onze besteldatum in oplopende volgorde wordt gesorteerd.

Daarna gaan we terug en maken we een tweede Group By aan .

Deze keer klikken we op de knop Geavanceerd , groeperen op Maand & Jaar en voegen vervolgens een tweede groepering toe.

De tweede groepering is voor IsBusinessDay , zodat we degenen kunnen nummeren waar werkdagen waar zijn.

Vervolgens maken we een AllRows-functie zodat deze niet wordt geaggregeerd en we terug kunnen gaan naar het oorspronkelijke granulariteitsniveau.

Dit zou ons geneste tabellen moeten geven waar we voor elke maand een tabel voor waar en een tabel voor onwaar kunnen zien .

Een aangepaste kolom maken

Vervolgens moeten we een aangepaste kolom maken om het aantal toe te voegen dat we nodig hebben voor de werkdagen.

We gaan een functie gebruiken met de naam Table.AddIndexColumn , die zal werken op de AllRows-tabel , en we noemen het Day Index . We willen dat de index begint met 1 en elke nieuwe werkdag met 1 wordt verhoogd.

Dan kunnen we de eerste drie kolommen verwijderen en onze vierde aangepaste kolom uitbreiden.

Als we naar het resultaat kijken, geeft het ons de dagindex, die de werkdagen telt (true), en vervolgens de dagen telt die weekends en feestdagen zijn in elke maand (false). Vervolgens moeten we controleren of deze in het juiste veldtype staan.

Laten we teruggaan naar onze verkooptabel. We voegen de samengevoegde tabel die we zojuist hebben gemaakt samen met onze verkooptabel.

We zullen die samenvoegen op de OrderDate . Je ziet onderaan dat het overeenkomt met alle rijen uit de eerste tabel.

Wanneer we de kolom Sales Agg uitvouwen, selecteert u de nieuwe velden die we zojuist hebben toegevoegd: de kolommen Month & Year , DayOfWeekName , MonthnYear , IsBusinessDay en DayIndex .

Laten we ook onze gegevens opschonen voordat we op Sluiten & Toepassen klikken . Laten we de Sales Agg-tabel in onze sectie Gegevensvoorbereiding verplaatsen en die tabel vervolgens uitladen omdat we deze niet nodig hebben in het gegevensmodel. Klik vervolgens op Sluiten & Toepassen .

Ons voorbereidende werk voor de Power Query is nu voltooid.

Een What-If-parameter creëren

Onthoud dat we in de oorspronkelijke taak het aantal dagen waarnaar we kijken willen variëren van 5 tot 10 en van 15 tot 20. Ik denk dat de beste manier om dit te doen is met een nieuwe wat-als- parameter .

We noemen de wat-als-parameter Eerste N werkdagen en het gegevenstype is een geheel getal. Het minimum is ingesteld op een toename van 1 en een maximum van 20. Dan gaan we standaard naar 5, wat de eerste waarde is die de gebruiker wilde.

We hebben nu een slicer die automatisch de oogstmaat maakt om de slicerwaarde vast te leggen.

Laten we een tabel maken met onze resultaten. Laten we onze dimensie Maand & Jaar in het canvas plaatsen en deze vervolgens sorteren op Maand & Jaar .

Zodra we dit veld goed hebben gesorteerd, kunnen we de meting Totale verkoop gewoon laten vallen en uitbreiden.

Een maat creëren voor het aantal werkdagen

We moeten nog een meting maken die kijkt naar de waarde van de schuifregelaarparameter en alleen berekent voor het aantal werkdagen.

We noemen deze nieuwe maat Totale verkoop N busdagen . We beginnen met de functie, aangezien we zeker de context veranderen, en met onze Total Sales-maatstaf. We gaan dit filteren met de van onze Verkooptabel.

De volgende stap is het schrijven van de voorwaarden waarmee we rekening willen houden voor de schuifregelaar. We hebben onze Sales Day Index die we hebben gemaakt in Power Query, en we willen dat deze kleiner is dan of gelijk is aan de eerste N werkdagen-waarde, wat de geoogste waarde van de slicer is.

Als de schuifregelaar bijvoorbeeld op 5 staat, willen we alle dagen van de tabel Verkoop waarop de dagindex kleiner is dan of gelijk is aan 5.

En dan willen we de niet-werkdagen eruit halen. We verwijzen naar de tabel Datum en gebruiken het veld IsBusinessDay. We kijken alleen naar het resultaat dat WAAR is.

Breng ten slotte de context terug voor Maand & Jaar en sluit de meting af. We zouden op dit punt goed moeten zijn om te gaan.

Laten we de nieuwe maat in onze tabel plaatsen.

We kunnen de slider-slicer naar de 10-daagse markering brengen en de tabel zal dynamisch berekenen op de 10-daagse markering.

Conclusie

In deze LuckyTemplates-tip hebben we het gehad over het dynamisch uitvoeren van een like-for-like-vergelijking met behulp van een Power Query-oplossing. We kunnen dit in een staafdiagram of een lijndiagram plaatsen.

In enkele van onze komende tutorials bekijken we de DAX-oplossing en implementeren we de visualisatie van de KPI-scroller.

Als je genoten hebt van de inhoud die in deze specifieke tutorial wordt behandeld, vergeet dan niet je te abonneren op het LuckyTemplates TV- kanaal.