Zaman Hesaplamaları -1, Kümülatif Hesaplama

İstisnasız tüm Power BI modellerinde bize lazım olan metrik gruplarından biri de zamana göre karşılaştırma metrikleri; önceki aya göre artış, önceki yıla göre varyans ya da geçen yılın tamamına göre neredeyiz gibi metrikler ve kümülatif hesaplamalar! Zaman periyotlarına göre -ki periyot gün, ay, çeyrek ya da yıl olabilir- karşılaştırma için bize lazım olan fonksiyon grubu “time intelligence” grubundaki fonksiyonlar.

Bu fonksiyon grubunun doğru çalışması için bize lazım olan bir tablo var: Olmazsa olmazımız Tarih tablosu. Tarih tablolarının taşıması gereken özellikler şöyle:

• Gün seviyesinde bir tarih sütunu içermeli ve bu sütunun veri tipi “date” olmalı
• İlgili yıllara ait tüm günleri eksiksiz olarak içermeli, arada eksik gün olmamalı.

Tarih tablosundaki diğer tüm geri kalan sütunlar aslında bu tarih sütununun varyasyonlarıdır, ilgili tarihin yılı, ayı, haftası, günü gibi. Daha önceki yazıda paylaştığım, bize istediğimiz tarih tablosunu oluşturacak bir şablonunuz yoksa CALENDARAUTO veya CALENDAR gibi fonksiyonları kullanarak kendi tablomuzu oluşturabiliriz.

CALENDARAUTO, model içerisinde tarih bilgisi içeren -hesaplanmış sütunlar hariç- tüm sütunları bulur ve bu sütunlardaki minimum ve maksimum yıllara göre her yıl için 365 günü içeren tek sütunluk bir tablo oluşturur.

Modelling tabından New Table diyerek aşağıdaki formülle böyle bir tablo yaratabiliriz.

Takvim = CALENDARAUTO()

Satışlar tablomuzda 2007-2009 yılları arası tarihler varken CALENDARAUTO 1.01.1910 ‘dan başladı ! CALENDARAUTO ‘yu kullanışlı olmaktan çıkaran da bu malesef, modeldeki tüm tarih sütunlarına bakıyor ve modelimizde 1910’lu tarihleri içeren müşterilerin doğum tarihi sütunu var. Şu sütunları dikkate alma gibi bir parametresi yok bu fonksiyonun.

CALENDAR fonksiyonunu kullanalım:

Takvim =
CALENDAR (
    DATE ( YEAR ( MIN ( 'Satışlar'[Tarih] ) ) ; 1 ; 1 ) ;
    DATE ( YEAR ( MAX ( 'Satışlar'[Tarih] ) ) ; 12 ; 31 )
)

Satışlar tablosundaki minimum ve maksimum yılı bulup, minimum yıl 1.1 ile başlayacak şekilde, maksimum yıl da 31.12 ile bitecek şekilde iki tarih arasındaki tüm günleri içeren tek sütunluk bir tablo oluşturmuş olduk. Bu tabloda bize lazım olan diğer sütunlar, bu sütunun varyasyonları. Aşağıdaki formülleri kullanarak hesaplanmış sütunlar olarak bunları oluşturabiliriz.

Date sütununun ismini “Tarih” olarak değiştirdim veri tipini de “Date” yaptım.

Yıl = YEAR( 'Takvim'[Tarih] )

Ay No = MONTH( 'Takvim'[Tarih] )

Haftanın Günü = WEEKDAY( 'Takvim'[Tarih] ; 2 )

Çeyrek No = INT( FORMAT( 'Takvim'[Tarih] ; "q" ) )

Çeyrek = "Q" & FORMAT( 'Takvim'[Tarih] ; "q" )

YEAR ve MONTH fonksiyonları gayet açık, bir tarih sütunu verdiğinizde  ilgili tarihin yılını ve ayını buluyor.

WEEKDAY’de benzer şekilde, ilgili tarihin haftanın kaçıncı günü olduğunu buluyor. Formüldeki ikinci parametre -örnekte 2- haftanın ilk günü hangi günden başlıyor ve sıralamayı 0’dan mı yoksa 1’den mi başlatacağımızı belirliyor. Parametreyi 2 verdiğimizde ilk günü Pazartesi kabul ediyor ve 1-7 diye günleri numaralandırıyor. Diğer opsiyonlar da zaten 1 veya 3 olabilir, hafta Pazar günü başlasın 1-7 diye gitsin istiyorsanız 1, hafta Pazartesi başlasın ama numaralandırma 0-6 diye gitsin istiyorsanız 3 dememiz gerekiyor.

Çeyrek No formülündeki FORMAT aynen Excel’deki gibi. Başındaki INT ‘in amacı da , FORMAT fonksiyonunun döndürdüğü “text” tipindeki değeri tam sayıya çevirmek.

Son formül ise & operatörü ile basit bir string birleştirme.

ADDCOLUMN fornksiyonuna bir örnek olması açısından yukarıdaki formülleri tek seferde de yazabiliriz! ADDCOLUMN, adı üstünde bir tabloya sütun eklemeye yarıyor. Giriş örneği olarak burada dursun.

Takvim =
ADDCOLUMNS(
    CALENDAR(
        DATE( YEAR( MIN( 'Satışlar'[Tarih] ) ) ; 1 ; 1 ) ;
        DATE( YEAR( MAX( 'Satışlar'[Tarih] ) ) ; 12 ; 31 )
    ) ;
"Yıl" ; YEAR( [Date] ) ;
"Ay No" ; MONTH( [Date] ) ;
"Haftanın Günü" ; WEEKDAY( [Date] ; 2 ) ;
"Çeyrek No" ; INT( FORMAT( [Date] ; "q" ) ) ;
"Çeyrek" ; "Q" & FORMAT( [Date] ; "q" )
)

Her ne kadar yukarıdaki gibi fonksiyonlarla bir tarih/takvim tablosu oluşturabilsek de ben size hazır bir şablon yaratmanızı/kullanmanızı önereceğim. Benim kullandığım şablon bu, Power Query’de yazılmış bir kod üzerinden çalışıyor, dilerseniz SQLBI’cıların tarih şablonuna da bir göz atın, daha esnek bulabilirsiniz.

Tarih tablosuyla ilgili yukarıdaki kurallara ek olarak yapmamız gereken bir şey daha var: Modelling tabından “Mark As Date Table” ile tabloyu ve tablodaki tarih sütununu tanıtmak. Time intelligence fonksiyonlarının doğru çalışması için bu şart.

Tarih tablolarındaki ay ve gün isimlerini sıralamayla ilgili sorun yaşıyorsanız bu yazıya lütfen.

Transaction (fact) tablolarında birden fazla tarih sütununuz varsa bu yazıya göz atmak isteyebilirsiniz.

Nizami bir tarih tablomuz varsa time intelligence grubundaki fonksiyonların kullanımı gayet kolay.

Kümülatif olanlarla başlayalım, yani aylık kümülatif, çeyrek kümülatif ve yıllık kümülatif gibi.  Her zamanki gibi temel bir satış metriğimiz var ve CALCULATE ile birlikte bu temel metriği time intelligence fonksiyonlarıyla birlikte kullanacağız.

Satışlar metriği ile aylık kümülatif satışları yazalım.

Satışlar := SUM(  'Satışlar'[Tutar] )

Satışlar Aylık Kümülatif := 
CALCULATE(  [Satışlar] ; DATESMTD(  'Tarih'[Tarih]  ) )

Aylık kümülatifin gün seviyesinde mantıklı olacağı açık, 2007 Ocak satırında gözüken rakam 31 Ocak 2007’deki kümülatif rakam. Ay seviyesinde Satışlar metriğiyle aynı şeyi gösterecektir.

Fakat gene de davranışı anlamak adına yıl ve ay bilgisiyle matrise düşürelim.

Ay bazında her iki metrik de beklendiği gibi aynı. 2007 toplam satırında gösterdiği rakam 2007 Aralık ayının satışları. En dip toplamda gösterdiği rakam 2009 Aralık ayının satışları.

DATESMTD’nin açıklamasında şu yazıyor:

Return a set of dates in the month up to current date. Current Date, mevcut güncel tarih diyelim, 2007 satırı için 2007’in en son satış yapılan günü, yani Aralık ayına ait günler, dolayısıyla Aralık 2007 kümülatifini hesaplıyor.

Benzer şekilde en dip toplam için current date, 2009’un son satış yapılan günü,  bu günün içinde olduğu ayın -yani 2009 Aralık’ın tarihlerini döndürüyor ve bu tarihlerdeki satışları kümülatif olarak topluyor.

Bu davranışı başlangıçta garip bulsak da, aslında fonksiyon tam da çalışması gerektiği gibi çalışıyor. RANKX’in dip toplamda 1 göstermesi gibi bir şey. Fakat doğru !

Hatta durumu biraz daha garipleştirelim! Tarih tablosunda, 2007-2009 yılları vardı yukarıdaki örnekleri yaparken. Tabloya 2010’u da ekliyorum. Matrisin dip toplamı bir şey göstermiyor bu sefer!

En dip toplamdaki “current date” 31 Aralık 2010 Aralık, dönen tablo Aralık 2010, fakat bu tarihlerde satış yok! Hesaplayacak bir şey de yok sonuç olarak.

Bu durumu kozmetik olarak düzeltmenin yolu, aylık kümülatif formülünü sadece matriste gün veya ay varsa çalışacak şekilde değiştirmek.

Satışlar Aylık Kümülatif :=
IF ( HASONEVALUE ( 'Tarih'[Tarih] ) || HASONEVALUE ( 'Tarih'[Ay] );
    CALCULATE ( [Satışlar]; DATESMTD ( 'Tarih'[Tarih] ) )
)

Çeyrek ve yıllık kümülatif metriklerini de ekleyelim:

Çeyrek Kümülatif :=
IF (
    HASONEVALUE ( 'Tarih'[Tarih] ) || HASONEVALUE ( 'Tarih'[Ay] )
        || HASONEVALUE ( 'Tarih'[Çeyrek] );
    CALCULATE ( [Satışlar]; DATESQTD ( 'Tarih'[Tarih] ) )
)

Yıllık Kümülatif := CALCULATE( [Satışlar] ; DATESYTD( 'Tarih'[Tarih] ) )

Çeyrek kümülatif formülündeki HASONEVALUE ‘ların gerekçesi de benzer, kullanmazsak yıl seviyesindeki toplamlarda ilgili yılın son çeyreğindeki toplamı gösterecekti.

Buradaya kadar yazdığım metriklerin isimlendirme mantığıyla ilgili bir öneride bulunayım: biri Satışlar, diğerleri Çeyrek Kümülatif ve Yıllık kümülatif. Matrisi oluştururken elim bir yukarıya bir aşağıya hareket etti. Halbuki hepsini orijinal metriğin farklı versiyonu olarak yazsaydım daha kolay bulurdum hepsini. Bu yüzden DATESYTD, DATESQTD ve DATESMTD fonksiyonların kolay yazımlı versiyonları olan TOTAL… karşılıklarını bu şekilde yazacağım.

Satışlar MTD :=
IF( HASONEVALUE( 'Tarih'[Tarih]) || HASONEVALUE( 'Tarih'[Ay]) ;
    TOTALMTD( [Satışlar] ; 'Tarih'[Tarih] )
)

Satışlar QTD :=
IF (
    HASONEVALUE ( 'Tarih'[Tarih] ) || HASONEVALUE ( 'Tarih'[Ay] )
        || HASONEVALUE ( 'Tarih'[Çeyrek] );
    TOTALQTD ( [Satışlar]; 'Tarih'[Tarih] )
)

Satışlar YTD := TOTALYTD( [Satışlar] ; 'Tarih'[Tarih] )

Sonuçlar aynı olacaktır, sadece TOTAL… versiyonların yazımı daha kolay.

Bir sonraki yazıda kümülatif hesaplamalara devam edeceğim.

İyi seneler.

Yazıdaki modeli indirebilirsiniz.

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