Som van laatste saldo met ontbrekende waarden

Stroomvariabelen zijn variabelen die de stroom van iets over een bepaalde periode weergeven. In ons geval vertegenwoordigt de transactie de afname of toename van het bedrag dat is toegewezen aan een rekeningcode over een bepaalde periode, bijvoorbeeld binnen een maand. Transacties betreffen de beweging van geld naar of van een bankrekening, en ze vinden plaats in een bepaalde tijdsperiode.

Voorraadvariabelen zijn variabelen die de totale hoeveelheid van iets op een bepaald moment weergeven. In ons voorbeeld is de Balance-maatstaf een toestandsvariabele, omdat deze de huidige toestand van onze rekening beschrijft. Het is een momentopname van de fondsen op de rekening op een bepaald moment. Voorraadvariabelen worden meestal gemeten op een specifiek moment in de tijd, zoals aan het einde van een maand of een jaar.

Stel dat de waarden van de voorraadvariabelen worden opgeslagen in een Fact Table.

Stel verder dat de voorraadvariabele wordt onderscheiden door een of meer dimensies. Bijvoorbeeld

• u hebt meerdere kasrekeningen, elk met een saldo
• of u hebt voorraadniveaus van verschillende artikelen

Stel verder dat de voorraadvariabele met onregelmatige tussenpozen wordt gemeten. Bijvoorbeeld

• normaal gesproken krijgen we één kassaldo per maand. Maar soms krijgen we er meer dan één per maand. En soms ontbreekt er een maand.
• normaal gesproken inventariseren we elk kwartaal, maar soms slaan we dat over.

Stel ten slotte dat de ontbrekende waarden van de ene dimensiewaarde kunnen verschillen van de andere. Bijvoorbeeld

• voor kasrekening A krijgen we een saldo op 31. Jan 2023 en 31 Maart 2023, terwijl we voor kasrekening B een saldo krijgen op 1. Jan 2023 en op 28. Maart 2023. Jan 2023 en op 28. Februari 2023.
• ofwel, de inventaris voor magazijn A wordt gemaakt in januari en de inventaris voor magazijn B wordt gemaakt in februari.

Nu willen we ons totale saldo berekenen, voor elk moment in de tijd.

Impliciet nemen we aan dat "geen nieuws = geen verandering". Dus, wanneer we geen nieuwe waarden van onze voorraadvariabele hebben, dan is de laatste waarde nog steeds geldig. Hier is het belangrijk om te begrijpen dat deze "fill-down" eigenschap per dimensie is.

We willen nu een DAX-maatregel die voor elk moment de som van het niveau van de aandelenvariabele berekent, rekening houdend met de laatste waarde van de aandelenvariabele, per categorie.

Voorbeeld

U kunt hier een pbix met de gegevens downloaden:

We kijken naar saldi van bankrekeningen.

We hebben een heel eenvoudig sterschema:

Stel dat de gegevens in onze feitentabel er als volgt uitzien:

In een niet-vertakte weergave hebben we:

Als we dit per rekening invullen, krijgen we:

We kunnen dan eenvoudig het totale kassaldo voor elke datum berekenen door de rekeningen bij elkaar op te tellen:

We willen de kolom berekenen en weergeven in Power BI.Totaal

Oplossing

We bereiken de oplossing in een paar stappen:

1. Index: we voegen een indexkolom toe in Power Query om onze transacties te ordenen

2. Transactie: dit is een berekende kolom die de verandering van de bedragen tussen maanden toont (stijging of daling), per rekening

3. Saldo: deze maatstaf telt de transacties tussen periodes op

Transactie

Transactie = 

 Voegt een berekende kolom toe die de transacties (stroomvariabele) berekent uit de saldi (voorraadvariabele) 
   VAR _cur_idx = 'FactAmount'[Index]

 VAR _balances_for_account = CALCULATETABLE('FactAmount', ALLEXCEPT('FactAmount', 'FactAmount'[Account]) ) 
   VAR _before_date = FILTER ( _balances_for_account, [Index] < _cur_idx )

 VAR _last_balance_tbl = TOPN ( 1, _before_date, [index] ) 
 VAR _last_balance = SELECTCOLUMNS( _last_balance_tbl, "@Balance", [Bedrag] ) 
   VAR _transactie = FactAmount[Bedrag] - _last_balans

   VAR _trx = IF(FactAmount[AccountType] = "T", FactAmount[Bedrag], _transactie)

RETURN

   _trx

Met deze kolom krijgen we de toename of afname tussen twee transacties per RekeningCode:

Nice!

Saldo

Saldo = 

 berekent het saldo als de som van alle voorgaande (berekende) transacties, per rekening 
   var _max_date = LASTDATE(DimCalendar[Date])   

   var _amnt = CALCULATE( SUM(FactAmount[Transactie]), VERWIJDERFILTERS(DimCalendar), KEEPFILTERS(DimCalendar[Datum] <= _max_datum))

   retourneer _amnt

De truc zit in de combinatie van `REMOVEFILTERS` en `KEEPFILTERS`. Dus als de filtercontext bijvoorbeeld februari 2023 is, wordt de filtercontext gewijzigd in elke datum vóór de laatste datum van februari.

In een notendop: voorheen leidden we de transacties (stroomvariabele) af uit het saldo. Nu berekenen we opnieuw het saldo (toestandsvariabele) uit de afgeleide transacties.

Voor ons voorbeeld levert dit het volgende op:

Aggregatie

Aangezien het omgaan met stroomvariabelen veel eenvoudiger is dan het omgaan met toestandsvariabelen, kan de nieuwe maatstaf voor balans worden opgeteld.

Filteren op

Tot nu toe hebben we op geen enkele rekening gefilterd. En in het voorbeeld van de G/L ADM hebben we veel rekeningen, waarvan er maar een paar geldrekeningen zijn.

Daarom moeten we filteren.

Dit doen we door berekeningen te gebruiken op de maatstaf 'Saldo'.

We kunnen bijvoorbeeld een 'Cash'-maatregel als volgt maken:

Cash = CALCULATE([Saldo], DimAccount[AccountMainGroupCode] = 10)

We kunnen gemakkelijk meer filters toevoegen als dat nodig is, bijv.

Geldmiddelen = CALCULATE([Saldo], DimAmountType[BedragTypeCode] = "B", DimAccount[AccountMainGroupCode] = 10)

Laatste gedachten

DAX is krachtig. Maar soms is het zo moeilijk.

Je zou denken dat de vereisten die in dit artikel worden beschreven veel voorkomen. Maar googelen helpt niet.

Het bouwen van deze DAX-metingen is ook verrassend moeilijk.

Om deze redenen hebben we bij Power Partners een bibliotheek met patronen opgebouwd. Laat het ons weten als je soortgelijke patronen hebt die nuttig kunnen zijn voor de community!