Tüm modellerde, zamanla ilgili kümülatif hesaplamalara ek olarak dönem karşılaştırma metrikleri de istisnasız lazım oluyor. Önceki aya göre, önceki yıla göre, önceki yılın tamamına göre gibi metrikler her modelin içermesi gereken metriklerden.
Uyarımı son kez tekrarlayayım, Tarih tablosu her modelin vazgeçilmez tablosudur ve özel bir tablodur. Transaction tablolarındaki tüm tarihleri içermesi tek başına yetmez, transaction tablolarında geçen yıl(lar)a ait tüm günleri eksiksiz bir şekilde içermelidir ve modele tarih tablosu olarak tanıtılmalıdır (Mark as Date Table).
Dönem karşılaştırmaları için birden fazla fonksiyon var kullanabileceğimiz, benim tercihim DATEAADD fonksiyonunu kullanmak. Hem esnek hem de pratik. Bu fonksiyonun 3 parametresi var:
DATEADD ( Tarih sütunu ; Dönem Sayısı ; Dönem Tipi )
Dönem, gün, ay, çeyrek veya yıl olabilir. Tek fonksiyonla Satışlar Önceki Ay, Satışlar Önceki Yıl gibi metrikleri yazabiliriz. ÖA (önceki ay), ÖY (önceki yıl) , ÖÇ, ÖG gibi kısaltmaları kullanıyorum genelde önceki dönemin ne olduğunu belirtmek için.
Aşağıdaki gibi metriklerimiz var:
Satışlar = SUM( 'Satışlar'[Tutar] )
Satışlar ÖA :=
CALCULATE([Satışlar] ; DATEADD('Tarih'[Tarih] ; -1 ; MONTH ) )
Satışlar ÖY :=
CALCULATE([Satışlar] ; DATEADD('Tarih'[Tarih] ; -1 ; YEAR ) )
Matrise düşürüyorum hepsini:
Her iki metriğe de “interval” -dönem- sayısı olarak olarak -1 verdik. DATEADD, mevcut satırdaki context’te gördüğü tarihler her neyse, bunu -1 dönem geriye “shift” edecek, yani kaydıracak.
Satışlar ÖA için bu cümleyi ilerletelim: 2007 Mart satırı için gördüğü context’teki tarihlerde 2007 Mart tarihleri var, bunu 1 dönem, yani 1 ay geri kaydıracak, yani 2007 Şubat tarihlerini görecek, dolayısıyla hesapladığı şey, 2007 Şubat ayı satışları olacak, hiç sorun yok.
Aynı cümleleleri Satışlar ÖY metriği için ilerletelim: 2009 toplam satırı için gördüğü context’te 2009 yılına ait tarihler var. Bunu -1 yıl geriye kaydırdığında gördüğü tarihlerde bu sefer 2008 yılına ait tarihler var, hesapladığı şey de dolayısıyla 2008 yılına ait satışlar. Burada da hiç sorun yok.
2008 yılı toplam satırı için, Satışlar ÖA ‘nın gösterdiği rakamın (4,208,163) ne olduğunu anlamaya çalışalım: bu satırda gördüğü context’te Ocak-Aralık 2008 dönemine ait tarihler var. Bu dönem aralığını -1 dönem, yani -1 ay geriye kaydırdığımızda, 2007 Aralık – 2008 Kasım aralığını buluruz, dolayısıyla metriğin hesapladığı şey bu kaydırılmış dönem aralığındaki satışlar.
Metrik çalışması gerektiği gibi çalışıyor, fakat kozmetik bir durum var. Satışlar ÖA, yıl seviyesinde mantıklı değil, ay seviyesinde mantıklı.
Benzer şekilde Satışlar ÖY, hem ay hem de yıl seviyesinde mantıklı, fakat satışların olmadığı 2010 yılı için göstermek çok uygun değil + en dip toplamda da göstermemek lazım.
Bu yüzden her iki metriği de aşağıdaki gibi modifiye ediyorum: satırda ay bilgisi varsa Satışlar ÖA hesapla + bulunduğumuz satırda Satışlar metriği 0’dan büyükse Satışlar ÖA ve Satışlar ÖY’yi hesapla + en dip toplamda Satışlar ÖY ‘yi gösterme
Satışlar ÖA =
IF( HASONEVALUE( 'Tarih'[Ay] ) && [Satışlar] > 0 ;
CALCULATE([Satışlar] ; DATEADD('Tarih'[Tarih] ; -1 ; MONTH )
)
)
Satışlar ÖY :=
IF( [Satışlar] > 0 && OR ( HASONEVALUE( 'Tarih'[Ay] ) ; HASONEVALUE( 'Tarih'[Yıl] ) ) ;
CALCULATE([Satışlar] ; DATEADD('Tarih'[Tarih] ; -1 ; YEAR )
)
)
Artık önceki aya ve yıla göre satışlardaki artışı tutarsal ve yüzde olarak hesaplayabiliriz.
Satışlar ÖA Artış # :=
IF( [Satışlar ÖA] > 0 ; [Satışlar] - [Satışlar ÖA])
Satışlar ÖA Artış % :=
DIVIDE( [Satışlar ÖA Artış #] ; [Satışlar ÖA] )
Satışlar ÖY Artış # :=
IF( [Satışlar ÖY] > 0 ; [Satışlar] - [Satışlar ÖY] )
Satışlar ÖY Artış % :=
DIVIDE( [Satışlar ÖY Artış #] ; [Satışlar ÖY] )
Formüllerdeki koşullar yukarıdakine benzer kozmetik durumları düzeltmek için.
Yoğun rakamların olduğu matrisler için her zaman “conditional format” kullanmanızı öneririm. Matrisin yorumlanmasını kolaylaştıracaktır.
Matrise düşürdüğümüz metrikleri sağ tıklayarak conditional format ayarlarına erişebiliriz, hepsi aynen Excel de olduğu gibi, ya da çok benziyor.
Benzer şekilde Satışlar Kümülatif metriğini yazalım ve bunun da önceki sene değerini bulalım.
Satışlar YTD := CALCULATE( [Satışlar] ; DATESYTD( 'Tarih'[Tarih] ))
Aynen yukarıdaki metriklerde olduğu gibi DATEADD fonksiyonunu kullanabiliriz. Fakat bir opsiyonumuz daha var: SAMEPERIODLASTYEAR. Adından da anlaşılacağı gibi, context’te gözüken tarih aralığı neyse, bu aralığın bir yıl öncesini görüyor, bir başka deyişle; aynı dönem önceki yılı görüyor.
Satışlar YTD ÖY :=
CALCULATE( [Satışlar YTD] ; SAMEPERIODLASTYEAR( 'Tarih'[Tarih] ) )
Önceki yılın kümülatifini [Satışlar ÖY] metriği üzerinden de hesaplayabiliriz, bu metriğin kümülatifini alabiliriz!
Satışlar YTD ÖY-2 :=
CALCULATE( [Satışlar ÖY] ; DATESYTD( 'Tarih'[Tarih] ) )
Aynı kapıya çıkacaktır.
* Birinde niye dip toplam gösteriyor da ötekinde göstermiyor, nasıl düzeltiriz konusunu size bırakıyorum.
…
Dönem karşılaştırma derken dönemin ne olduğunu da açmak gerek. Diyelim 2007 Şubat için önceki aya göre karşılaştırma yapıyoruz ve güncel tarih 10 Şubat 2007!
DATEADD fonksiyonu aynı gün aralığını yani, 1-10 Şubat 2007 ile 1-10 Ocak 2007 ‘yi karşılaştıracaktır. Peki ya önceki ayın tamamına göre karşılaştırmak istersek? Yani 1-10 Şubat 2007 ile 1-31 Ocak 2017’yi karşılaştırmak istersek?
Bu durumda kullanabileceğimiz iki fonksiyon var: bunlardan biri PARALLELPERIOD. Aynen DATEADD gibi bir yazımı var, yani istersek gün, ay, çeyrek, yılı dönem tipi olarak belirtebiliriz. Fakat bir farkı var, belirttiğimiz dönem sayısına göre dönemin tamamını alıyor!
Aşağıdaki metriği modele ekliyorum:
Satışlar_PARALLELPERIOD :=
CALCULATE( [Satışlar] ; PARALLELPERIOD( 'Tarih'[Tarih] ; -1 ; MONTH ) )
[Satışlar ÖA] ile birlikte matrise düşürüyorum:
Ay seviyesinde her ikisi de aynı gibi duruyor ama ayın gününü bir filtre olarak eklediğimizde fark ortaya çıkıyor.
[Satışlar ÖA] metriği gün filtresine riayet ederken PARALLEDPERIOD ‘lu formül riayet etmiyor ve önceki ayın tamamını göstermeye devam ediyor!
Benzer işlevi gören bir de PREVIOUSMONTH fonksiyonu var, bu da önceki ayın tamamını görüyor.
Satışlar_PREVIOUSMONTH :=
CALCULATE( [Satışlar] ; PREVIOUSMONTH( 'Tarih'[Tarih] ) )
Tahmin edebileceğiniz üzere, PREVIOUSDAY, PREVIOUSQUARTER ve PREVIOUSYEAR varyasyonları da var.
** Önceki ayın tamamına göre karşılaştırmayı çok sık kullanmayabilirsiniz ama önceki yılın tamamına göre karşılaştırmaları her zaman modelinize eklemenizi öneririm. Bu sene önceki yılın tamamına göre neredeyiz sorusu her zaman geçerlidir.
…
Aynen kümülatif hesaplama fonksiyonlarında olduğu gibi, dönem karşılaştırma fonksiyonlarında da bir hafta karşılaştırma fonksiyonu doğrudan yok. Haftanın tanımı sabit değil, 53-54 hafta olan yıllar var, artık yıllar var vs.
Yazı yeterince uzun oldu, bu sebeple hafta karşılaştırmayı da sonraki yazılara bırakıyorum.
Yazıdaki modeli indirebilirsiniz.
Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!