Zaman Hesaplamaları -1, Kümülatifler

İ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. 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 kayıtlı üyeler görebilir. Giriş veya Üyelik için login.

“Zaman Hesaplamaları -1, Kümülatifler” üzerine 9 yorum

  1. Merhaba,
    Tarih sütununu
    Takvim =
    CALENDAR (
    DATE ( YEAR ( MIN ( 'Satışlar'[Tarih] ) ) ; 1 ; 1 ) ;
    DATE ( YEAR ( MAX ( 'Satışlar'[Tarih] ) ) ; 12 ; 31 )

    bu şekilde yapasına kadar örneğin tarihle işimiz satış tablosundaysa satış tablosundaki tarih sütunundaki en küçük ve en büyük değerleri kullanmak daha efektif olma mı hatta maks kısmını +100 gün gibi fazladan gün de ekletileceği gibi en büyük değerin yılın son günü de yapılabilir ve satış datamız değiştikçe örneğin dosyadan eski yıllara ait satışları çıkarttığımızda ve yeni satışlar eklenmeye devam ettiğinde dahi sürekli güncel bir takvm tablosuyla çalışabiliriz bçylece

    • Satış tablosundaki tarih sütununun minimum ve maksimum yılını alıyor zaten kod? Yıl değil de minimum maksimum tarih (gün) değeri olarak diyorsanız hayır doğru olmaz. Tarih tablosunun içerdiği yıllara ait tüm günleri eksiksiz içermesi lazım. Mark as Date Table diye de işaretlenmesi lazım. Yoksa formülünüz yanlış sonuç verebilir. Fazladan gün eklemek istiyorsanız, gün değil yıl ekleyin.

      • Burada bahsettiğim yıl değil örneğin şuanki satış datam 2017 ilk gününden başlıyor ve her gün yeni günün satışını ekliyorum bu şekilde de data her gün güncel aralığı taşıyor ve min max formülleriyle de en büyük en küçük aralık şeklinde burayı belirttiğim için aradaki tüm günleri alıyor.

        • Dediğim gibi Tarih tablosu özel bir tablodur ve yukarıdaki özellikleri taşıması gerekir. Min gün Max gün aralığını bulmak doğru bir yaklaşım değil. Transaction'ların gerçekleştiği yılın tüm 365 gününü içermeli tarih tablosu. O gün satış olsun olmasın, o gün henüz gelmiş olsun gelmemiş olsun farketmez.

  2. merhaba,
    mark as date table olarak işaretlediğim zaman kullandığım bir özellik kayboluyor. o da şu: date sütununu matriste bir alana attığım zaman visualizations alanındaki values kısmında tarihi yıl/çeyrek/ay/gün olarak alt sınıflara ayırabiliyor. mark as date table olarak işaretlenince bunun kaybolmasının bir nedeni var mı acaba?

    • Mark as Date Table ekranında da belirttiği gibi, bir tablo takvim tablosu olarak işaretlendiğinde arka plandaki yerleşik (built in ) tarih tabloları siliniyor. Bahsettiğiniz hiyerarşi de bir tarih sütunu olduğunda bu yerleşik tarih tablolarını kullanan bir özellik. Yerleşik tablolar gidince bu da gidiyor haliyle. Bunun yerine yıl altına çeyrek ay gün sütunlarını sürükleyip bırakarak kendi hiyerarşinizi oluşturabilirsiniz.

Yorum yapın