LY en YTD calculaties in combinatie met een onvoltooid jaar in Power BI

Wanneer je met Power BI werkt kun je heel simpel gebruik maken van time intelligence functions bij het maken van calculaties. Zo bereken je met SAMEPERIODLASTYEAR() snel een waarde voor dezelfde tijdsperiode, maar dan een jaar eerder. En DATESYTD() kun je gebruiken om een cumulatieve waarde tot de huidige datum te berekenen binnen hetzelfde jaar. Als je deze twee functies combineert krijg je dus de cumulatieve waarde tot de huidige datum, maar dan een jaar eerder. Dit kan handig zijn om, bijvoorbeeld, de opbouw van de omzet cijfers van het huidige jaar te vergelijken met die van het vorige jaar. Als je echter met een onvoltooid jaar te maken hebt kan dit voor problemen zorgen in je visualisaties. Deze blog laat zien hoe je LY en YTD calculaties kunt gebruiken in combinatie met een onvoltooid jaar in Power BI.

YTD en LY YTD berekenen

In de volgende voorbeelden ga ik de Net Sales YTD en Net Sales LY YTD berekenen met de volgende code:

Net Sales YTD = 
CALCULATE ( 
    [Net Sales], 
    DATESYTD ( 'Date'[Date] ) 
)

Net Sales LY YTD = 
CALCULATE (
    [Net Sales] , 
    CALCULATETABLE (
        SAMEPERIODLASTYEAR ( 'Date'[Date] ),
        DATESYTD ( 'Date'[Date] ) 
    )
)

Onderstaand zie je dat de Net Sales YTD berekening voor april bestaat uit de som van Net Sales voor de maanden januari tot en met april. Daarnaast is de Net Sales YTD waarde van december hetzelfde als het totaal van Net Sales voor 2008.

Hieronder vind je de Net Sales LY YTD. Gezien 2008 het eerste jaar is kunnen er voor dit jaar geen waarden weergegeven worden. Voor het jaar 2009 kun je zien dat de waarden exact overeenkomen met de waarden die bij Net Sales YTD voor 2008 weergegeven worden. Zo zie je dat de totale Net Sales 4,3 miljoen euro is in juni 2009, vergeleken met 4,5 miljoen een jaar eerder.

Hoe verder in het geval van een onvoltooid jaar?

Wanneer het laatste jaar in je visual echter nog niet voltooid is kan dit voor verwarring zorgen. Om dit te illustreren heb ik de dataset gemanipuleerd. De laatste datum met sales data is nu 14 juli in 2009. Hieronder zie je de hoe de visual er nu uit komt te zien.

Ondanks dat er nu geen Net Sales en Net Sales YTD waarden zijn voor de maanden augustus tot en met December genereren beide YTD measures wel degelijk voor het hele jaar waarden. De maand juli in 2009 is daarnaast niet volledig (de data loopt tot 14-07-2009) terwijl de Net Sales LY YTD voor juli 2009 wel de volledige waarde van een jaar eerder laat zien. Niet echt een eerlijke vergelijking dus. We kunnen dit oplossen in een aantal stappen.

Laten we allereerst het rode blok oplossen. Op het moment dat er geen nieuwe Net Sales waarden zijn hoef ik de cumulatieve waarde ook niet te zien. Je zou dit simpel kunnen oplossen door te checken of er in de huidige filter context Net Sales gerapporteerd word:

Net Sales YTD = 
IF ([Net Sales],
    CALCULATE( 
        [Net Sales], 
        DATESYTD( 'Date'[Date] ) 
    )
)

Voor de Net Sales LY YTD doe ik hetzelfde, maar ga ik variabelen gebruiken:

Net Sales LY YTD 2 = 
VAR __SalesCheck = [Net Sales]
VAR __Result = 
    IF ( 
        __SalesCheck,
        CALCULATE ( 
            [Net Sales] , 
            CALCULATETABLE ( 
                SAMEPERIODLASTYEAR ( 'Date'[Date] ) , 
                DATESYTD ( 'Date'[Date] )
            )
        ) 
    )
RETURN __Result

LY YTD uitvoeren tot laatste datum met Net Sales

Nu is het tijd om de waarde van Net Sales LY YTD 2 in juli 2009 aan te pakken. Ik zou hier graag de YTD van juli 2008 zien tot 14 juli in plaats van 31 juli. Dit om een eerlijke vergelijking mogelijk te maken. Dit doe ik door een nieuwe variabele toe te voegen met de laatst zichtbare datum in de huidige filter context:

Last Visible Date = MAX ( Sales[Order Date] ) 

Deze variable geef ik vervolgens door aan de CALCULATETABLE() statement die bepaald voor welke data de Net Sales berekend wordt. Om te laten zien hoe deze laatst zichtbare datum werkt heb ik deze tijdelijk toegevoegd aan de tabel:

Zoals je kunt zien wordt de datum van 14-7 correct wordt weergegeven in de filter context van juli 2009. Tijd om deze variable te gaan gebruiken in onderstaande formule:

Net Sales LY YTD 3 = 
VAR __LastVisibleDate = MAX ( Sales[Order Date] ) 
VAR __Result = 
    CALCULATE ( 
        [Net Sales] , 
        CALCULATETABLE ( 
            SAMEPERIODLASTYEAR ( 'Date'[Date] ) , 
            DATESYTD ( 'Date'[Date] ), 
            'Date'[Date] <= __LastVisibleDate 
        ) 
    ) 
RETURN __Result

Nu laat Net Sales LY YTD 2 de YTD van juli 2008 zien tot en met 14 juli in plaats van 31 juli.

Wellicht heb je opgemerkt dat de eerder toegepaste check op Net Sales niet meer aanwezig is in de formule. Doordat we nu kijken of een datum in de huidige filter context voor de laatste order datum ligt is dit niet meer nodig. Ook het totaal is nu verbeterd. Doordat de laatst zichtbare datum in de totaal rij ook gelijk is aan 14-7-2009 wordt hier nu de YTD tot 14-07-2008 weergegeven in plaats van 31-12-2008.

Wanneer je het totaal geheel wilt verwijderen kun je nog een kleine aanpassing doorvoeren in de formule. Ik heb dit gedaan door een variabele die checkt of er meerdere jaren aanwezig zijn in de huidige filter context. In de totaal rij is dit het geval, namelijk 2008 én 2009. Vervolgens berekent de formule alleen een waarde als dit niet het geval is:

Net Sales LY YTD 4 = 
VAR __LastVisibleDate = MAX ( Sales[Order Date] ) 
VAR __NotGrandTotal = HASONEVALUE('Date'[Year])
VAR __Result = 
    IF ( __NotGrandTotal ,
        CALCULATE ( 
            [Net Sales] , 
            CALCULATETABLE ( 
                SAMEPERIODLASTYEAR ( 'Date'[Date] ) , 
                DATESYTD ( 'Date'[Date] ),
                'Date'[Date] <= __LastVisibleDate
            )
        ) 
    )
RETURN __Result

Conclusie

Ik hoop dat deze blog je heeft geholpen een beter begrip te krijgen hoe je YTD en LY calculaties kunt gebruiken in combinatie met een onvoltooid jaar in Power BI. Mocht je geintereseerd zijn in onze andere blogs dan kun je die hier vinden.