Power BI ile Bütçe Raporlama , Gerçekleşen ve Tahmin Projeksiyonu

** Uzun yazıdır. Ama bütçelerle çalışıyorsanız okumanızı tavsiye ederim!

Yaptığım veya danışmanlık verdiğim projelerin büyük kısmı Power BI ile bütçe raporlama üzerine, satış bütçesi, finans bütçesi, satınalma, stok vs diye gidiyor. Bütçeler aslında gayet kolay modellerdir, finansı bunun biraz dışında tutuyorum, çünkü finansal matrisleri standart görsellerle yaratmak hem biraz modelleme hem de DAX tecrübesi istiyor. Veri yapısının durumuna göre birkaç farklı şekilde modellenebilir. Finansal modellerin standart olmayan matrislerini kolay modellemek için yaratılmış custom görseller de var, fakat almaya kalktığınızda birkaç bin doları/euro’yu gözden çıkartmak gerekiyor.

Bütçe modellerinin çoğunda -şirket/sektör bağımsız- ortak durumlar ve talepler var. Bunlardan biri bütçe datasının oluşturulmasıyla ilgili, diğeri de bütçenin yapısı-mantığı ile ilgili.

Son projemdeki bütçe datası ve mantığı bugüne kadar gördüğüm örneklerin “nirvanası” olduğu için bu ve bundan sonraki yazıyı bütçeler özelinde yazmak istedim.

Önce bütçe datasıyla ilgili yazayım. Bütçenin veri modeline uygun nihai hali gayet kolaydır, yukarıda linkini verdiğim örneğe bakabilirsiniz. Her bir boyut kombinasyonu tarih sütunu/ayı altında -unpivot edilmiş olarak- listelenir.

Bütçe datasının oluşturulması ise bazen bambaşka bir dünya!

Şöyle bir senaryo düşünün, birden fazla ülkede veya şehirde faaliyet gösteren bir şirketsiniz, ya da bir çok departmanı olan bir şirketsiniz, sizden personel gideri/maaşlar gibi bir kalem için bütçe oluşturmanız isteniyor. Bütçeyi yapabilmek için geçmiş gerçekleşen verilere bakmanız gerekir genelde. Elinizde done olmadan bütçe tahmininde bulunamazsınız. Misal, geçen iki senenin aylık personel maaşlarına bakarsınız, bu sene kaç kişi alacağınızı iyi kötü belirlersiniz, geçmiş maaşlara enflasyon/zam oranı eklersiniz ve bir tahminde bulunursunuz. Bu verileri girebilmek için ortak bir format oluşturmanız gerekir, herkesin kendi birimine göre geçmiş datayı görmesi gerekir ve en nihayetinde bütçe sahipleri kendi birimlerine ait veriyi girer, siz de -developer olarak- bunları konsolide edip uygun şekle sokarsınız.

Veri girişleri genelde Excel ortamında. En yüksek teknolojiyi kullanan şirketler de bile bu böyle ! Gayet de doğal bir durum. (4-5 yıl öncesine ait “bütçe konsolidasyon” yazılımlarına neredeyse milyon dolar ödeyen şirketler aklıma geldikçe gülsem mi ağlasam mı bilemiyorum.)

Fakat bu Excel’i kim nasıl oluşturacak, daha da önemlisi kimler nasıl edit’leyecek bazen büyük bir sorun!

Şu örneği vereyim, belki size de tanıdık gelecektir!

Her bir boyut kombinasyonu ayrı ayrı Excel sayfalarına işlenmiş, aynı sayfada hem geçen senelere ait gerçekleşen verilen var, hem de bütçe tahminleri var. “Al sana bütçe” diye verilen bu Excel ile ilgili şunları da ekleyeyim, nasıl şenlikli bir durum olduğu daha net anlaşılsın: Excel dosyasında 140 civarında farklı bütçe sayfası var, her bir sayfa ise 60-70 sütun içeriyor (geçmiş datalar, bütçe tahminleri vs). Herkes kendi sorumluluğundaki boyut kombinasyonunu dolduruyor, sonra bu dosyalar emaille gönderiliyor falan! İnanılmaz boyutta gereksiz bir trafik, manuel hataya açık yüzlerce işlem! En güncel dosyanın kimde olduğunu bulmak bile zor!

“Her bir kombinasyonun gerçekleşenlerini bu dosyaya nasıl alıyorsunuz?” sorumun cevabı ise “sistemden bakıp elle giriyoruz” oldu! Bu durumda dosyanın nihai halini oluşturmanın birkaç hafta sürdüğünü öğrenmek pek sürpriz olmadı tabii!

Şaka gibi, ama değil, gerçek!

Bu kısmı daha çok uzatmayayım, demek istediğimi anlatabilmişimdir yeterince. Önerimi de ekleyip bitireyim, bu tür ortak edit’lenen dosyaları en kötü Sharepoint’te paylaşın, daha da güzeli, hem geçmiş verileri gösterdiğiniz hem de bütçe datasını girmeye imkan veren bir PowerApps+PowerBI uygulaması oluşturup, Power BI ortamında kullanıcılara verin!

Mevcut teknolojileri işlevsel hale dönüştürebilmek önemli, yok yok, çok önemli! Aksi taktirde yaptığımız manuel işlemlere bir de dijital hammaliye yüklemiş oluyoruz.

…

Bütçe datasını her nasıl yaptıysanız yaptınız diyelim, modelleme yaklaşımınıza göre veriyi genellikle aşağıdaki iki şekilden birine sokarız:

İki yaklaşım da gayet iyi çalışır, verinizin durumuna ve ihtiyaçlarınıza göre bazen biri diğerine göre daha avantajlı olabilir. İkinci yapıyla ilgili bu yazıya bir göz atabilirsiniz.

Şirketler bütçe yaparken genellikle 2 farklı tipte bütçe verisi giriliyor: Bir tanesi sene başında 12 ay için yapılan ve genellikle sabitlenen -yani sene boyunca değiştirilmeyen- “Plan”. Diğeri ise bazen aylık bazen birkaç aylık dönemlerde güncellenen “Tahmin”. Bu tahmin kısmı için de genellikle şöyle bir durumla karşılaşıyorum: Mayıs Haziran’a kadar herhangi bir tahmin girilmiyor, gerçekleşenler Plan verisine göre karşılaştırılıyor. Ama gerçekleşenlerin gidişatına göre Mayıs-Haziran’dan itibaren “tahmin” (forecast, artık sizdeki jargon neyse) verileri girilmeye başlanıyor.

Örnek data da böyle, 12 ay için sabit “Plan” rakamları var, “Tahmin” ise Haziran’dan itibaren var.

Genel ihtiyaçlardan biri şu: Diyelim şu an Eylül ayındayız, sene sonunda “Plan” veya “Tahmin” ‘e göre nerede olacağız? Arkasında da şöyle bir mantık var: Eylül ayına kadar olan gerçekleşenler + önümüzdeki ayların “Plan” veya “Tahmin” datası bizim sene sonundaki durumumuzu gösterir!

Bir başka deyişle: geçmiş aylar için hesap yaparken, o aya ait bir tahmin/plan varsa, bunu o ayın gerçekleşeni ile değiştir!

Örnekte ikinci yapıyı kullanacağım. Daha önce birkaç yazıda belirtmiştim, tekrar edeyim, ay-hafta seviyesinde veriniz varsa, bu tür durumları modellemenin en kolay yolu ilgili ay-hafta için bir referans tarih atamaktır. Ocak için 1 Ocak 2021, Şubat için 1 Şubat … gibi. Yani veriyi aşağıdaki formata sokmuş oldum.

Biraz daha zor gibi gözüktüğü için “Tahmin” üzerinden devam edeceğim. Her ay için “Plan” datası var, ama “Tahmin” datası yok çünkü.

Basit metriklerle başlayıp adım adım istediğimizi bulmaya çalışalım. Aşağıdaki metrikleri modele ekliyorum.

Gerçekleşen := 
CALCULATE(
    SUM( 'Gerçekleşen vs Bütçe'[Tutar] ) ,
    'Gerçekleşen vs Bütçe'[Tip] = "Gerçekleşen"
)

Tahmin := 
CALCULATE(
    SUM( 'Gerçekleşen vs Bütçe'[Tutar] ) ,
    'Gerçekleşen vs Bütçe'[Tip] = "Tahmin"
)

Ve bir matrise düşürüyorum.

“Sene Sonu Neredeyiz” metriği, ay bazında Ocak-Eylül dönemi için gerçekleşen rakamı göstermeli, Ekim-Aralık dönemi içinse Tahmin rakamını göstermeli, toplam satırında da doğru toplamı göstermeli, yani Ocak-Eylül Gerçekleşenler + Ekim-Aralık Tahminler.

Basit bir IF testi ile yapılır gibi görünüyor: [Gerçekleşen] varsa (sıfırdan büyükse) gerçekleşen değerini döndür, değilse [Tahmin] değerini döndür.

Sene Sonu Neredeyiz? :=
IF( [Gerçekleşen] > 0 ,
    [Gerçekleşen] ,
    [Tahmin]
)

Ay bazında doğru çalışan metriğimiz yıl satırında beklediğimiz gibi çalışmıyor! Sebebi filter context’te ne gördüğümüzle ile ilgili (her zaman olduğu gibi) !

Yıl satırında gördüğümüz context’te tüm aylar var, dolayısıyla yıl satırında tüm aylara daha doğrusu tüm tarihlere ait [Gerçekleşen] toplamı ile [Tahmin] toplamını görüyor. İkisi de sıfırdan büyük, bu yüzden -condition’da ilk sırada [Gerçekleşen] > 0 ise dediğimiz için- toplam gerçekleşen rakamını döndürüyor.

Filter context’te ne gördüğünü bir metrikle bulursak daha kolay anlaşılacak bu durum:

Sene Sonu Neredeyiz Neyi Görüyor? := 
COMBINEVALUES( "," , [Gerçekleşen] , [Tahmin] )

Yıl satırı için ayrı bir mantık kurup, yıl satırındaysak şöyle yap ay satırındaysak böyle yap diye atraksiyona girebiliriz.

Bunun yerine benzer, fakat tarihi baz alan başka bir mantık kullanalım: En son gerçekleşen tarihini bul, bu tarihten sonraki tüm tarihlerde “tahmini” referans al, önceki tarihlerde gerçekleşeni referans al!

LASTDATE veya MAX fonksiyonlarını bu son tarihi bulmak için kullanabiliriz. Deneyelim!

Son Gerçekleşen Tarih := 
CALCULATE(
    LASTDATE( 'Gerçekleşen vs Bütçe'[Tarih] ) ,
    REMOVEFILTERS( Tarih ) ,
    'Gerçekleşen vs Bütçe'[Tip] = "Gerçekleşen"
    -- REMOVEFILTERS yerine ALL da kullanabiliriz
)

Gerçekleşen en son tarihi bulduk, bu tarihten sonraki tüm tarihler için tahmini, önceki tarihler içinse gerçekleşeni döndür diyelim! Formülü bir değişkene atayarak kullanacağım.

Sene Sonu Neredeyiz -1 := 
VAR _SonGerceklesenTarih =
CALCULATE(
    LASTDATE( 'Gerçekleşen vs Bütçe'[Tarih] ) ,
    REMOVEFILTERS( Tarih ) ,
    'Gerçekleşen vs Bütçe'[Tip] = "Gerçekleşen"
    -- REMOVEFILTERS yerine ALL da kullanabiliriz
)

VAR _Sonuc =
IF( 
    LASTDATE( Tarih[Tarih] ) > _SonGerceklesenTarih ,
    [Tahmin] ,
    [Gerçekleşen]
)
RETURN
_Sonuc

Matrise düşürelim!

Ay bazında doğru çalışan metrik, yıl bazında gene istediğimiz sonucu vermiyor! İlk yaptığımız denemedeki aynı durumla karşı karşıyayız aslında, LASTDATE ( Tarih[Tarih] ), yıl satırı için 31.12.2021. Bu değer 01.09.2021’den büyük mü, evet büyük, o zaman [Tahmin] değerini döndür! Bu da 2.550!

LastDate := 
LASTDATE( 'Tarih'[Tarih] )

Artı, ince bir nüansa da dikkatinizi çekeyim, Eylül için hem gerçekleşen tarih var hem de tahmin, eğer Eylül henüz tam kapanmadıysa, yani Eylül ortasındaysak misal, bu metrik gerçekleşen tarihin ne olduğuna göre tahmin değerini de döndürebilir gerçekleşen yerine!

Mantığı biraz değiştirip, filter context’in yıl satırında ne gördüğünü de düşünerek formülü yeniden yazalım: Filter context’te gördüğün Tarih tablosunu filtrele, sadece _SonGerceklesenTarih ‘ten sonraki tarihler için tahmini döndür!

Sene Sonu Neredeyiz -2 := 
VAR _SonGerceklesenTarih =
CALCULATE(
    LASTDATE( 'Gerçekleşen vs Bütçe'[Tarih] ) ,
    REMOVEFILTERS( Tarih ) ,
    'Gerçekleşen vs Bütçe'[Tip] = "Gerçekleşen"
    -- REMOVEFILTERS yerine ALL da kullanabiliriz
)
VAR _Gerceklesen = [Gerçekleşen]

VAR _Tahmin =
CALCULATE( 
    [Tahmin] ,
    FILTER( Tarih ,
        Tarih[Tarih] > _SonGerceklesenTarih 
    )
)

VAR _Sonuc = _Tahmin + _Gerceklesen

RETURN
_Sonuc

Metrik, yıl satırı da dahil olmak üzere istediğimiz gibi çalışıyor!

CALCULATE fonksiyonuna henüz aşina değilseniz, kafanızı karıştırmak pahasına FILTER kullanmayıp doğrudan tarih karşılaştırması yapsak ne olurdu?

Sene Sonu Neredeyiz -3 := 
VAR _SonGerceklesenTarih =
CALCULATE(
    LASTDATE( 'Gerçekleşen vs Bütçe'[Tarih] ) ,
    REMOVEFILTERS( Tarih ) ,
    'Gerçekleşen vs Bütçe'[Tip] = "Gerçekleşen"
    -- REMOVEFILTERS yerine ALL da kullanabiliriz
)
VAR _Gerceklesen = [Gerçekleşen]

VAR _Tahmin =
CALCULATE( 
    [Tahmin] ,
    Tarih[Tarih] > _SonGerceklesenTarih 
    )
VAR _Sonuc = _Tahmin + _Gerceklesen

RETURN
_Sonuc

Bu tamamen başka bir kalıp oldu! _Tahmin değişkeni, filter context’i -ay satırlarında- tamamen değiştirip, sadece _SonGerceklesenTarih ‘ten sonraki tüm tahminleri topluyor, yani Ekim-Kasım-Aralık tahmin toplamını döndürüyor!

RETURN sonrasında geçici olarak _Tahmin’i döndürelim, daha net anlaşılır.

Tahmini Döndür := 
VAR _SonGerceklesenTarih =
CALCULATE(
    LASTDATE( 'Gerçekleşen vs Bütçe'[Tarih] ) ,
    REMOVEFILTERS( Tarih ) ,
    'Gerçekleşen vs Bütçe'[Tip] = "Gerçekleşen"
    -- REMOVEFILTERS yerine ALL da kullanabiliriz
)
VAR _Gerceklesen = [Gerçekleşen]

VAR _Tahmin =
CALCULATE( 
    [Tahmin] ,
    Tarih[Tarih] > _SonGerceklesenTarih 
    )
VAR _Sonuc = _Tahmin + _Gerceklesen

RETURN
_Tahmin

Yukarıdaki _Tahmin kodlarını bir arada görelim :

Soldaki CALCULATE kalıbı, mevcut filter context’i koruyarak çalışıyor! Bulunduğun satırdaki ay/yıl için gördüğün tarihler neyse, bu gördüğün tarihlerden _SonGerceklesenTarih’ten büyük olanlarını döndür.

Sağdaki CALCULATE kalıbı, mevcut filter context’i tamamen bozuyor ve kendi context’ini yaratıyor. Bulunduğun satırda Ocak’mı var, Şubat’mı var yoksa yıl mı var umursamıyorum, bana sadece _SonGerceklesenTarih’ten sonraki tarihleri döndür.

CALCULATE’in kalıpları için bu yazıya göz atmak isteyebilirsiniz.

Soldaki kalıbı KEEPFILTERS fonksiyonunu ile de yazabiliriz! KEEPFILTERS, adı üstünde, mevcut filter context’in korunmasını sağlayacak!

Sene Sonu Neredeyiz -4 := 
VAR _SonGerceklesenTarih =
CALCULATE(
    LASTDATE( 'Gerçekleşen vs Bütçe'[Tarih] ) ,
    REMOVEFILTERS( Tarih ) ,
    'Gerçekleşen vs Bütçe'[Tip] = "Gerçekleşen"
    -- REMOVEFILTERS yerine ALL da kullanabiliriz
)
VAR _Gerceklesen = [Gerçekleşen]

VAR _Tahmin =
CALCULATE( 
    [Tahmin] ,
    KEEPFILTERS(Tarih[Tarih] > _SonGerceklesenTarih )
    )
VAR _Sonuc = _Tahmin + _Gerceklesen

RETURN
_Sonuc

Sene Sonu Neredeyiz 2 ve 4, aynı mantıkta çalışıyor, ve dönen sonuç bizim istediğimiz sonuç.

Metriğin -daha anlamlı olması için- sadece ismini değiştiriyorum:

Projeksiyon := 
VAR _SonGerceklesenTarih =
CALCULATE(
    LASTDATE( 'Gerçekleşen vs Bütçe'[Tarih] ) ,
    REMOVEFILTERS( Tarih ) ,
    'Gerçekleşen vs Bütçe'[Tip] = "Gerçekleşen"
    -- REMOVEFILTERS yerine ALL da kullanabiliriz
)
VAR _Gerceklesen = [Gerçekleşen]

VAR _Tahmin =
CALCULATE( 
    [Tahmin] ,
    KEEPFILTERS(Tarih[Tarih] > _SonGerceklesenTarih )
    )
VAR _Sonuc = _Tahmin + _Gerceklesen

RETURN
_Sonuc

Son olarak modele hem Gerçekleşen için hem de Projeksiyon için YTD (Year-to-Date) metriklerini ekliyorum.

Gerçekleşen YTD := 
CALCULATE( [Gerçekleşen] , DATESYTD( 'Tarih'[Tarih] ) )

Projeksiyon YTD := 
CALCULATE( [Projeksiyon] , DATESYTD( 'Tarih'[Tarih] ) )

ve matrise düşürüyorum.

Gerçekleşen ve Projeksiyon metrikleri ve bunların YTD versiyonları, Ocak-Eylül için aynı. Sonrasında değişiyor. Ekim ve sonrasında sadece tahmin var.

Gerçekleşen ve Projeksiyonu bir Linechart’a düşürelim.

Buradaki temel espri, önce Projeksiyon, sonra Gerçekleşen metriğini yerleştirip, her iki serinin de görünümüyle/rengiyle uğraşmak!

Eğer Gerçekleşen’i üste Projeksiyon’u alta koyarsanız biraz kötü bir görüntü elde ederiz!

Serileri birbirinden farklılaştırmak için de format panosundaki “Data Colors” , “Data Labels” ve “Shapes” bölümleriyle oynayabiliriz.

Aynı şeyi YTD’ler için de yapabiliriz.

Sonraki bir ya da iki yazı gene bütçelerle ilgili olacak.

** Güncelleme : 17.12.2021

Yazıyı yayınladıktan sonra gerçekleşenin olmadığı tarihler için YTD’yi nasıl göstermeyebiliriz sorusu geldi birkaç farklı takipçiden. Bu tür “problemlerin” tek bir doğru çözümü yok, birden fazla yöntem var her zaman, algoritmayı nasıl tasarladığımıza göre yazacağımız kod değişecektir.

İki farklı yöntem tarifleyeceğim, birinde metrikler üzerinden gidelim, diğerinde de hesaplanmış sütun mantığından.

Metrikle çözmek için şöyle basit bir mantık kurabiliriz: “Gerçekleşen” metriğinin sıfırdan büyük olduğu aylar için YTD hesapla, “Gerçekleşen”yoksa, bir diğer deyişle BLANK ise , hesaplama yapma BLANK döndür!

Gerçekleşen YTD Modifiye = 
VAR
_YTD =
CALCULATE( [Gerçekleşen] , 
    DATESYTD( 'Tarih'[Tarih] )
)
VAR
_MevcutAyinSatisi =  [Gerçekleşen]

RETURN
IF( ISBLANK(_MevcutAyinSatisi ) ,
  BLANK() ,
  _YTD
)

“Son Gerçekleşen Tarih” metriğimiz var yazdığımız, onun üzerinden de gidilebilir! Dediğim gibi kodu kurduğumuz mantık belirliyor.

Hesaplanmış sütun mantığıyla da yapabiliriz: Tarih tablosuna hesaplanmış sütun ekleyelim. Bu sütun, tablodaki her bir tarihi, en son gerçekleşen tarihle karşılaştıracak, “Doğru” ya da “Yanlış” string’ini döndürecek. (TRUE FALSE fonksiyonları da kullanılabilir benzer şekilde.)

Yazdığımız orijinal YTD formülünü de bu sütunun mantığını kullanacak şekilde modifiye edebiliriz.

Gerçekleşen YTD Modifiye Hesaplanmış Sütun = 
CALCULATE( [Gerçekleşen] ,
    CALCULATETABLE( 
        DATESYTD( 'Tarih'[Tarih] ) ,
        'Tarih'[GerçekleşenVerisiYok] = "Yanlış"
    )
)
/* DATESYTD 'de gördüğün tarihleri, gerçekleşen verisinin olup olmadığı
tarihlere göre filtrele. YTD hesabını da sadece gerçekleşenin olduğu
tarihler için yap. */

Yazıdaki modeli -bloga üyeyseniz- indirebilirsiniz.

Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!