Aynen kümülatif zaman fonksiyonlarında olduğu gibi, dönem karşılaştırma fonksiyonlarında da haftalık hesaplama için kullanabileceğimiz doğrudan bir fonksiyon yok. Misal WTD gibi bir haftalık kümülatif alma gibi bir fonksiyon yok! Dolayısıyla hafta karşılaştırma metrikleri yazmak istiyorsak custom formüller yazmamız gerekecek!
En esnek DATEADD fonksiyonunda dönem (interval) olarak gün, ay, çeyrek veya yıl seçebiliyoruz. Ama hafta yok! Çünkü haftanın standart bir tanımı yok, gerekçesi de bu.
Kullandığım tarih tablosunda ilgili günün yılın hangi haftasına ait olduğunu gösteren bir “Hafta No” sütunumuz var. Yılın ilk haftasının ilk günü -benim tarih tabloma göre- ilgili yılın 1 Ocak tarihi.
Satışlar metriğini ve satırda gözüken context’e göre maksimum hafta no’yu hesaplayan bir metriği yıl-hafta numarası bazında matrise düşürüp bir mantık kuralım.
Satışlar := SUM( 'Satışlar'[Tutar] )
Max_Hafta No := MAX( 'Tarih'[Hafta No] )
Önceki haftanın satışlarını hesaplayabilmek için, satırdaki hafta no’nun gördüğü tarihleri (context’i), aynı yılın bir önceki haftasını görecek şekilde değiştirmemiz lazım: Tarih tablosundaki tüm filtreleri kaldır + tarih tablosunu matrisin satırındaki aynı yılın bir önceki haftasını görecek şekilde filtrele.
Satışlar Önceki Hafta :=
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih' ) ;
'Tarih'[Hafta No] = MAX( 'Tarih'[Hafta No] ) - 1 &&
'Tarih'[Yıl] = MAX( 'Tarih'[Yıl] )
)
)
** Bu kalıbın önemli bir kalıp olduğunu daha önce de belirtmiştim, bu yazıya göz atmak isteyebilirsiniz.
Doğru çalışıyor gibi gözüküyor fakat bir sorun var:
Devam eden yıl(lar)ın ilk haftası için doğru çalışmıyor!
Eğer yılın ilk haftası için hesaplama yapıyorsak, tarih context’ini bir önceki yılın en son haftası neyse onu bulacak şekilde modifiye etmemiz lazım.
Eğer HaftaNo =1 ise koşulu için bir formül yazmamız lazım, 1 değilse yukarıda yazdığımız formül doğru çalışacak zaten.
Yazacağımız formülü basitleştirmek adına sadece HaftaNo=1 ‘in TRUE olması durumunda ne yapmak istediğimizi yazalım: Tarih tablosundaki filtreleri kaldır + satırda yılın bir önceki yılına ait maksimum haftayı bul.
Satışlar ÖH Modifiye :=
IF( [Max_Hafta No] = 1 ;
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih' ) ;
'Tarih'[Hafta No] = CALCULATE( [Max_Hafta No] ; ALL( 'Tarih' ) ) &&
'Tarih'[Yıl] = MAX( Tarih[Yıl] ) - 1
)
)
)
MAX hafto no’yu hesaplamanın esprisi şu, örnek modelde tüm yıllar sanki 53 haftaymış gibi ama bu tesadüfi bir durum, yıl tarih tablonuzun yapısına ve yılın artık yıl olup olmadığına göre 52, 53 veya 54 hafta olabilir.
Formülü haftanın 1 ‘den farklı olması durumunda da çalışacak şekilde tekrar modifiye edelim + Hafta bilgisi varsa bu metriği göstermek mantıklı + satışlar varsa bu metriği hesaplıyor olmamız lazım.
Satışlar ÖH Modifiye :=
IF( HASONEVALUE( 'Tarih'[Hafta No] ) && COUNTROWS( 'Satışlar' ) > 0 ;
IF( [Max_Hafta No] = 1 ;
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih' ) ;
'Tarih'[Hafta No] = CALCULATE( [Max_Hafta No] ; ALL( 'Tarih' ) ) &&
'Tarih'[Yıl] = MAX( Tarih[Yıl] ) - 1
)
) ;
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih' ) ;
'Tarih'[Hafta No] = MAX( 'Tarih'[Hafta No] ) - 1 &&
'Tarih'[Yıl] = MAX( 'Tarih'[Yıl] )
)
)
)
)
Her ne kadar mantığı gayet basit de olsa uzun bir formül oldu!
Değişken yapısını kullanarak aynı metriği benzer bir mantıkla çok daha kolay okunur (yazılır) bir şekilde yazmak mümkün.
Satışlar ÖH Değişken :=
IF( HASONEVALUE( 'Tarih'[Hafta No] ) && COUNTROWS( 'Satışlar' ) > 0 ;
VAR _SatirdakiHafta = MAX( 'Tarih'[Hafta No] )
VAR _SatirsakiYil = MAX( 'Tarih'[Yıl] )
VAR _MaksimumHafta = CALCULATE( MAX( 'Tarih'[Hafta No] ) ; ALL( 'Tarih' ) )
RETURN
SUMX(
FILTER( ALL( 'Tarih' ) ;
IF( _SatirdakiHafta = 1 ;
'Tarih'[Hafta No] = _MaksimumHafta && 'Tarih'[Yıl] = _SatirsakiYil - 1 ;
'Tarih'[Hafta No] = _SatirdakiHafta -1 && 'Tarih'[Yıl] = _SatirsakiYil
)
) ;
[Satışlar]
)
)
Power BI ‘da bir şeyi yapmanın birden fazla yolu var genelde ; biraz kafamızın nasıl çalıştığıyla ilintili, biraz da kabiliyeti ve fonksiyonları bilmekle ilintili. Yukarıdaki formüllerde çözdüğümüz tek konu aslında yılın hafta numarasına göre değişik varyasyonlara girmek.
Başka bir yöntemle çözelim! İndeks kullanmaya da örnek oluştursun.
-Diyelim- 52. hafta tek başına tekil bir bilgi değil. Ama 2008 yılının veya 2009 yılının 52. haftası tekil bir bilgi. 2008-52, 2009-52 bir tane var! Yıl-Hafta kombinasyonunu indekslesek, yani bir sıra numarası versek, yukarıdaki formülleri çok daha kısaltabiliriz.
Power Query tarafında Tarih tablosunun bir kopyasını oluşturuyorum. (Tarih tablosu sağ tık, Duplicate)
Oluşan tablodan da sadece Yıl ve Hafta sütunlarını alıyorum ve bu iki sütun kombinasyonunun tekil listesini oluşturuyorum.
Bu tabloda şu an her bir satır kombinasyonu “tekil”. Her bir satıra 1’den başlayacak şekilde bir sıra numarası ekliyorum.
(Add Column + Index Column (From 1).
Tarih tablosu ile bu yeni oluşturduğumuz tabloyu “Yıl + Hafta No” sütunları üzerinden “merge” ediyorum, ki, tarih tablosundaki her bir satıra ilgili satırın haftasının sıra numarası gelsin!
OK dedikten sonra “Expand” ederek sadece İndex sütununu alıyorum.
Tarih tablosuna Yıl-HaftaNo kombinasyonunun sırasını getirdik.
Bu sütundaki sıra numarası yıl-haftano kombinasyonuna göre sürekli birer birer artan değerleri içeriyor.
Bir önceki indeks numarasına göre metriği modifiye etmek yeterli olacaktır artık.
Satışlar ÖH İndekse Göre :=
IF( HASONEVALUE( 'Tarih'[Hafta No] ) && COUNTROWS( 'Satışlar' ) > 0 ;
CALCULATE( [Satışlar] ;
FILTER( ALL( 'Tarih' ) ;
'Tarih'[Yıl-Hafta.Index] = MAX( 'Tarih'[Yıl-Hafta.Index] ) - 1
)
)
)
Yazdığımız kod 17 satırdan 8 satıra düştü ! Fakat tamamen başka bir mantık kurduk.
İndeksleme sadece bunun için değil buna benzer başka durumlar için de kullanabileceğimiz bir teknik.
** Power Query ile ilgili henüz yeterince yazı yazamamış olduğumdan ikinci bir sorgu oluşturup ilkiyle merge etmeyi tercih ettim kolay anlaşılması açısından. Table.NestedJoin ile tek sorguda bu işi yapmak da mümkün.
Yazıdaki modeli indirebilirsiniz.
Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!