In dit artikel laat ik je zien hoe je de ontwikkeling van personeelsverloop over tijd in Power BI visualiseert. Dit artikel gaat over DAX in Power BI, en is de 2e Q&A over Power BI vragen. Klik hier om Q&A artikel 1 te lezen waarom je altijd een datumtabel moet gebruiken in Power BI.
Probleem
Recent kreeg ik een vraag om het personeelsverloop per jaar/maand inzichtelijk te maken. De brondata (dummy data) zag er zo uit:
Een tabel dus met 3 kolommen: Naam_medewerker, Datum_in_dienst, Datum_uit_dienst. De eerste twee kolommen zijn altijd gevuld, de laatste kan leeg zijn (dan is de medewerker nog in dienst).
Gevraagd is een helder overzicht van instroom en uitstroom per jaar/maand. Samen resulteert dat dus in een netto getal per maand en een aantal medewerkers in dienst op een bepaald moment.
Oplossingsrichting
De klant had al geprobeerd om de kolommen Datum_in_dienst en Datum_uit_dienst op te tellen (count/aantal), dan krijg je dit:
Niet wat je wil: De getallen 13 en 8 zijn het totaal aantal medewerkers in dienst en uit dienst, en dat is niet uit te splitsen naar Jaar/Maand. Wat je wil is een totaal aantal voor bijvoorbeeld 2019, dat ziet er zo uit:
Hier zie je dat je Jaar/Maand kan uitsplitsen en optellen. Zo zijn op 01-11-2019 drie medewerkers gestart en zijn er vijf medewerkers op 30-11-2019 uit dienst gegaan. Netto dus min twee in november 2019.
Oplossing
Deze oplossing bereik je als volgt:
- Voeg een datumtabel toe aan je datamodel. In de vorige Power BI QA vind je waarom.
- Verbind de Datumtabel kolom Date (dus kolom 01-01-2019 ,02-01-2019 etc.) met je Datum_in_dienst kolom. Dit is een actieve relatie (vaste lijn).
- Verbind nu de Datumtabel kolom Date met je Datum_uit_dienst kolom. Dit is een inactieve relatie (stippellijn).
4. Nu maak je de volgende DAX metingen aan:
Indienst = COUNT( Medewerkers[Datum_in_dienst] )
Uitdienst = CALCULATE( COUNT(Medewerkers[Datum_uit_dienst]) ; USERELATIONSHIP( Medewerkers[Datum_uit_dienst] ; Datumtabel[Date] ) )
Netto = [Indienst] – [Uitdienst]
Bij de Meting Indienst tel ik het aantal rijen op uit de kolom Datum_in_dienst. Doordat ik filters gebruik (jaar/maand etc.) worden deze aantallen dynamisch aangepast aan de juiste periode.
Bij de Meting Uitdienst maak ik gebruik van zowel CALCULATE als USERELATIONSHIP. Hiermee geef ik expliciet aan dat in het datamodel niet de actieve maar de passieve relatie (de stippellijn) moet kiezen.
Netto is simpelweg het verschil tussen beide Indienst en Uitdienst.
Nu ben ik klaar met DAX en kan ik een rapport maken. Daarvoor gebruik ik zowel een Matrix tabel als een Waterval om het verloop inzichtelijk te maken:
Het PowerBI Desktop bestand met uitwerkingen vind je hier.
Vragen / opmerkingen over dit artikel? Stel zie hieronder als reactie. Heb je een andere Power BI Q&A vraag (zoals deze vraag over recente uurtarieven in Power BI)? Stuur me een berichtje via Linkedin.