Tijdinformatie in DAX: hoe u de startperiode dynamisch kunt selecteren

In deze blogpost zullen we enkele lastige problemen met tijdinformatie behandelen, met name die problemen die worden veroorzaakt door weekgranariteit en de problemen die kunnen worden veroorzaakt door het onregelmatige aantal weken in een jaar. We zullen offsets gebruiken om te komen met nauwkeurige tijdinformatie in DAX.

We hebben vandaag een interessant scenario voor je. Dit kwam onlangs een paar keer naar voren op het LuckyTemplates-forum. Wat mensen wilden doen, was een visual nemen (dit kan een lijngrafiek of een staafdiagram zijn) en deze dynamisch variëren op de startdatum. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

In dit geval gebruikten we dezelfde dataset die we gebruikten voor de gemiddelde spotprijs per vat olie . In plaats van altijd maar bij het begin in januari te beginnen, wilden we in staat zijn om op de visual te klikken, deze te wijzigen naar een andere startdatum, maar altijd een jaar aan gegevens te tonen.

Inhoudsopgave

• Voorbeeldscenario's van tijdinformatie in DAX
• Offsets gebruiken voor tijdinformatie in DAX
• Implementatie van de Rubber Duck-strategie voor tijdinformatie in DAX
• De offsets oogsten vanaf de startdatum
• Berekening van het maandelijkse bereik
• Berekening van de offsets van de begin- en eindmaand
• Het wekelijkse bereik berekenen
• Wekelijks de binnen bereik berekenen
• Conclusie

Voorbeeldscenario's van tijdinformatie in DAX

Er zijn een aantal redenen waarom u deze techniek zou willen doen. U kunt dit gebruiken als u een statistiek heeft die voortdurend wordt aangepast in termen van hoe deze wordt berekend. Misschien wilt u alleen de gegevens van de periode van de bijstelling naar voren laten zien.

Een andere reden om dit te gebruiken is als je dit geanimeerd wilt visualiseren. Dit neemt in feite uw gegevens en toont deze dynamisch zodra u op de afspeeltoegang klikt.

In deze visual kunnen we de startdatum van maand tot maand wijzigen om te laten zien hoe deze in de loop van de tijd verandert in een periode van 12 maanden. Dit is een interessante en nuttige visual voor verschillende businesscases, maar in theorie vertegenwoordigt het ook een aantal belangrijke kwesties met betrekking tot DAX en datamodellering.

Laten we naar LuckyTemplates gaan en eerst naar ons datamodel kijken. Dit is een heel eenvoudig gegevensmodel met een uitgebreide datumtabel en een spotprijstabel die aan de datums is gekoppeld.

Offsets gebruiken voor tijdinformatie in DAX

Een van de dingen waar we aan zullen werken, zijn offsets . De offsets in de uitgebreide datumtabel worden dynamisch berekend. Elke keer dat het rapport wordt vernieuwd of geopend, gaat het door de M-code. In een maandelijkse offset wijst het bijvoorbeeld de huidige maand toe met 0, de vorige maand met -1 en twee maanden terug met -2. Vooruitkijkend in de toekomst, kent het de volgende maand +1 toe, en voor twee maanden in de toekomst +2.

Dit is een eenvoudig concept, maar ongelooflijk krachtig als je te maken hebt met tijdinformatie in DAX en werkt met maanden , kwartalen en weken . Als u geen offset gebruikt, zorgt dit soms voor behoorlijk wat complexiteit in uw berekeningen.

Offsets gebruiken betekent een doorlopende reeks getallen gebruiken waarbij een maand teruggaan altijd -1 is en een maand vooruit altijd +1 is, ongeacht waar in het jaar u zich bevindt.

Implementatie van de Rubber Duck-strategie voor tijdinformatie in DAX

In het verleden heb ik het gehad over rubber ducking , waarbij je je strategie hardop uitspreekt voordat je begint met het schrijven van je DAX.

Ik ben hardop aan het nadenken over hoe ik dit ga aanpakken vanuit een maandelijkse context met behulp van time intelligence in DAX. Ik zou iets beginnen op basis van het geselecteerde jaar en de geselecteerde maand, en die selecteren met behulp van een niet-verbonden tabel, want als je erover nadenkt, zal elke andere selectie een overschrijding van jaren met zich meebrengen (met uitzondering van januari).

Laten we zeggen dat we 12 maanden willen vanaf maart, dan eindigen we met minimaal twee maanden in het volgende jaar. Als we een aangesloten slicer gebruiken, kunnen we alleen voor dat jaar filteren en niet voor het volgende jaar.

De offsets oogsten vanaf de startdatum

Laten we dit doen met een gekoppelde tabel voor zowel de maand als het jaar. Eerst moeten we de eerste offset oogsten die betrekking heeft op de startdatum.

En dan willen we die offset met 12 maanden naar voren schuiven, en dan alleen kijken naar de datums die binnen die reeks offsets vallen. Laat me je laten zien hoe dat eruit ziet in DAX.

Berekening van het maandelijkse bereik

Dit is onze maatstaf voor Within Range Monthly , waar we ons jaar selecteren (die we hebben geoogst uit de losgekoppelde jaartabel) en onze maand (die we hebben geoogst uit de losgekoppelde maandtabel).

We hebben ook deze andere parameter waarbij als er geen selectie wordt gemaakt, deze standaard wordt ingesteld op januari. Deze parameter is voornamelijk bedoeld voor foutopsporingsdoeleinden.

Berekening van de offsets van de begin- en eindmaand

Laten we eens kijken naar de beginmaandoffset door de MAX offset te berekenen. We verwijderen alle filters op de datumtabel en filteren naar de geselecteerde maand en het geselecteerde jaar. Voor elke maand mag er slechts één offset zijn die overeenkomt met die maand en dat jaar.

Vanaf dat moment kunnen we de offset van de eindmaand nemen , wat slechts de offset van de beginmaand + 11 maanden is.

En dan kijken we naar elke geselecteerde datum en bepalen of deze binnen de initiële maandoffset en de eindmaandoffset valt. als het binnen die periode valt, geven we het een 1 en zo niet, dan geven we het een 0.

Als we teruggaan naar de maandelijkse weergave in onze visual, kunnen we de Within Range- visual zien die we hebben ingesteld als gelijk aan één. Het toont dus alleen die maanden binnen de begin- tot eindoffset. Klikken we bijvoorbeeld op februari, dan zien we februari t/m januari.

Het wekelijkse bereik berekenen

Laten we eens kijken hoe dit eruit ziet vanuit een wekelijks standpunt. De visual begint aanvankelijk goed en gaat van week 1 tot week 52. Tot nu toe, zo goed.

Maar als we op de andere jaren klikken in de jaarslicer, kunnen we de 52e week zien, terwijl sommige een 53e week hebben, wat veel problemen oplevert. U kunt aan dit voorbeeld zien dat het probleem zich begint te manifesteren.

We hebben een startperiode van jaar 2020 en week 15 , maar de eindperiode is jaar 2021 en week 13 in plaats van jaar 2021 en week 14 .

Als we teruggaan naar week 1, kunnen we zien dat het goed begint, maar eindigt in week 52. Als we hier kijken naar week 53, is het maximale weeknummer voor 2020 en 2021 53 weken. Dit gaat niet werken voor wekelijkse granulariteit.

Laten we eens kijken wat we kunnen doen om dit te laten werken. We moeten de DAX-berekening hier in dit deel oplossen:

Dit komt omdat in sommige gevallen 51 correct is als er slechts 52 weken in een jaar zijn, maar voor het jaar met 53 weken wordt die laatste periode weggelaten. Dit is precies wat we wekenlang in de dynamische visual zagen, waar de berekening die laatste periode in jaren 2020 en 2021 verwijderde.

Om dit op te lossen, gaan we naar de Within Range Weekly Wrong -maatstaf, die er eigenlijk eenvoudiger uitziet dan de eerdere maatstaf. We hadden een beginnende offsetmeting en berekenden de maximale offset. Vervolgens hebben we het filter van datums verwijderd en vervolgens het filter op de geselecteerde week en het geselecteerde jaar gelegd in de veronderstelling dat dit tot de juiste offset zou leiden.

Maar dit werkt niet omdat het niet uitmaakt of u een maximale offset of een minimale offset gebruikt. Het enige wat we doen is een aggregator omwikkelen zodat we geen naakte kolom in een CALCULATE-instructie plaatsen.

Maar als we hier teruggaan naar het ISO-weeknummer , kunnen we zien dat een jaar en een weeknummer niet op unieke wijze een wekelijkse offset in die eerste periode bepalen.

Ik bedacht een kogelvrije manier om dit te doen. U kunt MIN gebruiken, maar het is logischer om deze Week1 Offset -meting te maken. Om de offset van week 1 te krijgen, hebben we te maken met week 2 omdat deze nooit wordt gesplitst. Ongeacht of er 52 of 53 weken in het jaar zijn, week 2 blijft intact.

In deze berekening filteren we naar week 2 om de offset te krijgen. En als we die offset van week 2 eenmaal hebben, trekken we er gewoon één van af. Dit geeft ons ondubbelzinnig de offset van week 1. Dit lost uiteindelijk ons ​​probleem op.

Wekelijks de binnen bereik berekenen

En dan gaan we terug naar onze wekelijkse meting binnen bereik en schrijven we een IF-verklaring waarin we, als het oogstnummer voor week 1 is, de offset van week één berekenen. Als het niet week 1 is, berekenen we gewoon de startweekverschuiving zoals we deden in de vorige maandelijkse berekening.

En dan is de offset van de eindweek de offset van de beginweek + het maximale weeknummer dat 52 of 53 kan zijn. Dan trekken we gewoon 1 af om de startoffset niet dubbel te tellen.

We kunnen dezelfde constructie gebruiken die we gebruikten voor het maandelijkse bereik om de weken te filteren, waarbij alles wat tussen de begin- en eindoffset valt een 1 krijgt en alles wat niet een 0 krijgt.

Vervolgens gaan we de wekelijkse meting binnen bereik in het filtervenster plaatsen. Alles checkt uit en ziet er precies goed uit.

We kunnen op de afspeelas klikken en de granulariteit van de week uitvoeren. We kunnen zien dat het goed werkt, net als in de maandcontext.

Conclusie

Dit is een vrij diepe duik in tijdinformatie in DAX, waar we bespraken hoe we enkele van de problemen rond weeknummers kunnen aanpakken. Ik hoop dat je deze tutorial nuttig vond en je wat extra tools in je gereedschapskist hebt gegeven als je te maken hebt met een problematische weeksituatie.

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. Er komt voortdurend een enorme hoeveelheid content uit van mijzelf en een reeks contentmakers, allemaal toegewijd aan het verbeteren van de manier waarop u LuckyTemplates en het Power Platform gebruikt.