Power BI disaggregatie van maand naar week

Bij veel klanten zien we dat budgetcijfers worden geboekt per volledige maand. Om deze data te combineren met de inkoopcijfers op datumniveau moeten we bewerkingen doen op de budgetdata, zoals het verdelen van de maandbudgetten over het aantal dagen van de maand. Hiermee kan er tijdens de maand al worden bijgestuurd op het budget, in plaats van wanneer de maand al om is. De SAP Front-End tools die we kunnen connecteren aan SAP BW of SAP HANA kunnen hier mee omgaan, maar hoe zit dat met non-SAP front-end tools? In deze blog geven we een uitwerking van deze business case voor Microsoft Power BI geconnecteerd aan een SAP BW systeem.

De budget data is verdeeld over de maanden van het jaar. Voor iedere maand is er met een key op maand-jaar een budget vastgesteld. In het rapport willen we deze budgetten vergelijken met inkoopdata die op het detailniveau van factuurdatum wordt bijgehouden. We willen per week weergeven of het inkoopbedrag binnen het budget van die week is gebleven.

De budgetdata ziet er als volgt uit:

Om de data te koppelen aan andere datums, moeten we een datumkolom toevoegen. Voeg hiervoor in Power BI een datumkolom “MaandJaar” toe met de volgende formule (in M):

"1" & "/" & [Maand] & "/" & [Jaar]

Het resultaat is dan als volgt:

Om deze data op weekniveau weer te geven, is er een extra datumdimensie nodig. Deze kan worden gemaakt met de formule CALENDAR() of CALENDARAUTO() en noemen we "Kalender".

CALENDARAUTO() bouwt automatisch een tabel op van alle datums in het model.
CALENDAR() bouwt een tabel op van een opgegeven start- en einddatum.

Aan deze Kalender zijn drie extra kolommen toegevoegd, “JaarMaand”, “Werkdag” en “Weeknr”:

JaarMaand = FORMAT( 'Kalender'[Date], "yyyy-MMM")
Werkdag = WEEKDAY( 'Kalender'[Date], 2 ) in {1,2,3,4,5}
Weeknr = WEEKNUM('Kalender'[Date],2)

JaarMaand is een formattering van het datumveld, werkdag is een boolean veld (true/false) om aan te geven of een datum op een werkdag valt en Weeknr is het nummer van de week in het jaar.

Ten slotte is er een berekening toegevoegd aan de Kalender tabel die het aantal werkdagen telt.

# Werkdagen = CALCULATE( COUNTROWS( 'Kalender' ), KEEPFILTERS( 'Kalender'[Werkdag] ) ) + 0

Deze berekening telt het aantal rijen in de Kalender, met inachtneming van het filter op de kolom werkdag.

Nu kunnen de Budget tabel en de Kalender tabel als volgt aan elkaar verbonden worden middels een één-op-veel relatie.

Wanneer het Budget per Weeknr wordt geplot in een lijngrafiek, ziet dit er als volgt uit:

De X-as bevat alleen de weeknummers waar de eerste dag van de maand in valt. Dit komt doordat er in de Budget tabel alleen waarden bestaan voor iedere eerste dag van de maand.

Om de budgetdata weer te geven over de tussenliggende datums, moet het budget worden verdeeld over het aantal (werk)dagen in die maand. Hiervoor is er een nieuwe berekening toegevoegd:

Budget per Week =
VAR __zichtbareDatums = VALUES( Kalender[Datum] )
RETURN
  SUMX(
    VALUES( Kalender[JaarMaand] ),
    CALCULATE(
      VAR __maandBudget = SUM(Budget[Budget])
      VAR __werkdagenPerMaand = [# Werkdagen]
      VAR __werkdagenPerMaandInSelectie =
        CALCULATE(
          [# Werkdagen],
          KEEPFILTERS( __zichtbareDatums )
        )
     VAR __budgetInMaandVoorSelectie =
        DIVIDE(
          __maandBudget * __werkdagenPerMaandInSelectie,
          __werkdagenPerMaand
        )
        RETURN
          __budgetInMaandVoorSelectie,
          ALLEXCEPT( Kalender, Kalender[JaarMaand] )
        )
      )

Deze berekening neemt voor elke unieke JaarMaand in de Kalender tabel het budget en het aantal werkdagen in die maand.
Vervolgens wordt berekend hoeveel werkdagen er in de gehele weergegeven periode zijn.
Het budget wordt nu berekend door maand budget * totaal aantal werkdagen / werkdagen in de maand waarvoor het budget geldt.
De berekening geeft vervolgens voor iedere datum het berekende budget weer.

Het eindresultaat is als volgt:

De budgetten zijn nu verdeeld over het aantal werkdagen per maand en weergegeven per week van het jaar. Wanneer we nu inkooporders toevoegen en aggregeren per week, kunnen deze op het zelfde aggregatieniveau worden vergeleken.

De inkoopmanagers kunnen nu per week zien of er binnen de budgetten wordt gebleven. Wanneer er in de eerste twee weken van de maand het budget is overschreden, kan er halverwege de maand worden bijgestuurd om de maand mogelijk met zwarte cijfers te eindigen.

Interesse?

Wil je de Power BI voorbeelduitwerking ontvangen om dit ook te gebruiken of wil je meer weten over hoe Power BI en andere non-SAP tools zijn toe te passen op SAP BW of SAP HANA, neem dan contact op met Roel van Bommel. 
Roel.Van.Bommel@mccoy-partners.com of (+31) 06 22 69 83 92