0 comments
Business Intelligence 
PowerPivot beschikt over een groot aantal Time Intelligence functies, zoals DatesYTD. Deze kun je gebruiken om gangbare berekeningen die aan tijd gerelateerd zijn te doen. Vorige week pas liep ik tegen iets aan waar ik in eerste instantie geen verklaring voor had en wat ook niet breeduit beschreven wordt, al helemaal niet in de officiële DAX documentatie.
In eerste instantie had ik de volgende, werkende DAX formule om een YTD uit te rekenen:
=CALCULATE(SUM(SalesOrderHeader[SubTotal]), DATESYTD(OrderDates[FullDate]))
Deze formule gaf correcte resultaten op de SalesOrderHeader tabel in AdventureWorks gelinkt aan een Excel tabel met datums op een kolom van het type DateTime. Toen ik iets soortgelijks probeerde op een PowerPivot model gebaseerd op het AdventureWorks datawarehouse waarin de tabellen FactInternetSales en DimTime gelinkt zijn op de surrogaatsleutel van DimTime, werkte mijn YTD niet.
=CALCULATE(SUM(FactInternetSales[SalesAmount]), DATESYTD(DimTime[FullDateAlternateKey]))
Pas na het opnemen van de extra filter ALL(DimTime), werkte de YTD ook in deze situatie zoals je zou verwachten.
Zoals ik al zei, helpt de documentatie op dit punt weinig. Maar gelukkig kon Marco Russo mij aan een goed antwoord helpen. Een antwoord dat ik na lang zoeken ook terugvond in een blog artikel van Jeffrey Wang. Wanneer één van de filters in de CALCULATE functie gebruik maakt van een unieke kolom van het type date/time, verwijdert CALCULATE alle bestaande filters op de tabel die die kolom bevat. In mijn tweede situatie gebruikte ik in de filter een surrogaatsleutel in de filter, en dan verwijdert CALCULATE de filter op de DimTime tabel dus niet. Gevolg is dat je datums terug krijgt binnen de huidige filtercontext op de tijdtabel en dus lang niet altijd vanaf het begin van het jaar… Bedenk ook dat de DATESYTD functie geen filter functie is (zoals FILTER en ALL, die dat wel zijn). De documentatie van de DATESYTD functie zegt heel duidelijk: “Returns a table that contains a column of the dates for the year to date, in the current context.”. Het is dus vooral de CALCULATE functie die iets extra’s doet wanneer je filtert op een datetime kolom. En dat heb ik in de documentatie niet terug kunnen vinden.
Moraal van dit verhaal is dat je toch echt altijd een extra filter op de tijdtabel op moet nemen om de filtercontext op die tabel te vergeten voordat je DATESYTD of vergelijkbare functies gebruikt, tenzij je relatie tussen feiten en tijddimensie op een datetime kolom ligt. Overigens zal de volgende versie van PowerPivot meer gebruik maken van metadata, waardoor het gedrag van CALCULATE zeer waarschijnlijk consistenter wordt, ook wanneer je niet filtert op een datetime kolom.