zaterdag 14 maart 2020




Next date of Previous date info met hulp van de DAX functie EARLIER()

Als je de beschikking hebt over een tabel met onregelmatige periodieke veranderingen van waardes, dan is het lastig om deze informatie te koppelen aan een andere tabel.

Bijvoorbeeld: een tabel met kostprijsmutaties van producten die op willekeurige data wijzigen.
Deze info wil je gebruiken om bijvoorbeeld een andere tabel met de dagelijkse productie aantallen te voorzien van de juiste kostprijs. Ik heb de beschikking over de tabel Price en een tabel Quantity  

         
                 
Ik wil de pricelist nu koppelen aan de kwantiteit om de waarde van de productie aantallen te berekenen. Helaas is er geen unieke sleutel te maken en dus moet het berekend worden.
Alvorens je dat kunt berekenen dient iedere rij een begin- en einddatum te hebben.
De begindatum is al bekend, de kolom einddatum is als volgt te berekenen:

NextDate=(CALCULATE(MIN(Price[date]);FILTER(Price;Price[date]>EARLIER(Price[date])&&Price[Category]=EARLIER([Category]))))

Als je de formule ontleed dan staat er eigenlijk:
Zoek de kleinste datum die groter is dan de huidige datum (de eerstvolgende datum dus), waarbij de Category overeenstemt.  De tabel ziet nu als volgt uit:

                          

De lege datumvelden zijn in de rijen met de hoogste Date per Category.
Om ook deze velden te vullen kun je hier eenvoudig een extra berekend kolom met de datum van vandaag invullen als volgt.

NextDateAll =if(Price[NextDate]<>0;Price[NextDate]-1;now())


Je hebt nu een begin- en einddatum RANGE gecreëerd waarmee je in de tabel Quantity de kostprijs kunt berekenen.

Eerst creëren we in de tabel Quantity een extra kolom om bij iedere productie regel de juiste Price in de datum RANGE op te vinden als volgt:

Price = CALCULATE(VALUES(Price[Price]);FILTER(Price;Price[Category]=Quantity[Category] && Quantity[Date]>=Price[Date] && Quantity[Date]<=Price[NextDateAll]))

En daarna eenvoudig :   Costprice =[Price]*[Quantity]

That's all!


Extra: PreviousDateAll
Mocht je op zoek zijn naar de datum van de vorige rij, previous row, dan is deze als volgt te vinden:

PreviousDate=CALCULATE(MAX(Price[date]);FILTER(Price;Price[date]<EARLIER(Price[date];1)&&Price[Category]=EARLIER([Category])))

En vervolgens als er geen eerdere datum is, is de begindatum gelijk aan de einddatum:
PreviousDateAll = if(Price[PreviousDate]<>0;Price[PreviousDate];Price[Date])

woensdag 25 september 2013

Combineren van meerdere databronnen in PowerPivot en de enorme efficiencyslag in tijd.

Afgelopen week werd me gevraagd mee te denken over een oplossing om naast commerciële stuurinformatie vanuit een CRM pakket ook financiële gegevens vanuit Exact te betrekken in één rapportage vanuit meerdere entiteiten.

Kortom, een leuke uitdaging om eens verschillende SQL databases met elkaar te koppelen via PowerPivot, gebruikmakend van PowerQuery, om de juiste informatie te masseren en samen te voegen.

Ik wist dat deze rapportages tot op heden handmatig middels plakken en knippen, filteren etc vanuit diverse bronnen werd samengevoegd. De rapportages hadden een frequentie van 1x per kwartaal en waren erg arbeidsintensief te noemen. Gemiddeld 2 weken per jaar werd eraan gewerkt.

De uiteindelijke format werd aan mij voorgelegd met de uitdrukkelijke wens om de stuurinformatie sneller en op maandelijkse frequentie te rapporteren.  Natuurlijk kost het tijd uit te zoeken waar de informatie in de verschillende tabellen staat opgeslagen en volgende deze juist te koppelen zodat er nuttige informatie ontstond.   Het koste me uiteindelijk 2x een halve dag om te ontwikkelen.

Ik kan nu met behulp van één slicer iedere klant of klantengroep selecteren voor een bepaalde periode en terugblikken op oude periodes en natuurlijk een grafische weergave van het verloop presenteren. Een report per klant opstellen kost me nu 10 seconden. 

Hieronder de uiteindelijke structuur in PowerPivot  met daarin :
1. CRM/verloningstabellen uit Flexservice SQL2005
2. gefilterde en gemasseerde tabellen uit Exact SQL2008
3. Hulptabel uit Excel




dinsdag 17 september 2013

SUMX & IF om een outlook van een jaar te berekenen

Een tijdje geleden kwam bij mij de vraag om een outlook te berekenen van actuals en budget data.
Beide tabellen hadden periodieke data en ik besloot om van de gerealiseerde periodes de actuals te combineren met de budgetcijfers van de niet gerealiseerde periodes. Dat deed ik met behulp van de measure outlook met de functie: IF actual = 0; budget; actual.

Een prima werkte measure totdat ik de tabel liet totaliseren. Wat Powerpivot doet is simpelweg bij de totaaltelling de totalen van de actual en budget vergelijken en (aangezien de som van de actuals niet 0 is) het saldo van actuals als totaal opgeven voor de tabel outlook.

Hoe kon ik nou de juiste outlook berekenen?

Hieronder in een voorbeeld de problemen en oplossing uitgewerkt:

figuur 1a: 3 eenvoudige tabellen , actuals, budget & periode

figuur 1b: als volgt gelinkt in Powerpivot


figuur 2: ik voeg de tabel actual en budget toe aan mijn report en heb een measure  gecreëerd voor de outlook. Ik noem hem  Outlook fout =if(SUM(Actual[actual])=0;sum(Budget[budget]);sum(Actual[actual]))
Je ziet hier dat periodiek juist wordt gekozen tussen actuals en budget, maar dat het eindtotaal van €70.000 niet klopt.Dat had € 255.000 moeten zijn.


 figuur3: de measure Outlook fout.


Ik heb op internet gezocht naar oplossingen, maar vond niet direct een vergelijkbaar probleem, wel een aantal aanwijzingen dat de oplossing ergens lag in een combinatie van SUMX en IF.

Na enige tijd kwam ik op de volgende measure voor een outlook:
Outlook beter = SUMX(VALUES(per[per]);IF(sum(Actual[actual])=BLANK();sum(Budget[budget]);sum(Actual[actual])))

Ik had ergens gelezen dat een SUMX icm IF, mits je de periodiek totaliseert, juist zou optellen.
Helaas klopte de totaaltelling van € 840.000   (12 x  het totaal van de actuals a € 70.000) niet met de uitkomst die ik zocht.
figuur 4


Ik besloot om twee extra measures te maken TotalActual en TotalBudget
TotalActual = sum(Actual[actual])  & TotalBudget =sum(Budget[budget]) en daarna verving ik in de outlook measure sum(Actual[actual]) voor TotalActual  & sum(Budget[budget]) voor TotalBudget het werkt!
Kennelijk moet je eerst een totaal berekenen en de uitkomst daarvan in een tweede meting te stoppen.

De measure voor de outlook wordt dan :
Outlook goed=SUMX(VALUES(per[per]);IF([TotalActual]=BLANK();[TotalBudget];[TotalActual]))

Figuur5 de oplossing met juiste outlook.