Sıklıkla karşımıza çıkabilecek şöyle bir ihtiyaç var raporlarda: Kullanıcının seçtiği belirli bir tarih aralığında satılan ürünlerin -diyelim- önceki ay, önceki yıl gibi farklı periyodlardaki performanslarını hesaplamak istiyoruz. Örneği ürün/satış diye yapacağım ama farketmez! Belirli bir tarih aralığında işlem gören, yani transaction (fact, hareket) tablosunda geçen item’ların (entity, varlık) farklı periyod performanslarını hesaplamak istiyoruz!
Örneği ve ihtiyacı biraz açalım:
- Kullanıcı rapor sayfasından belli bir tarih aralığını seçti: Bu örnekte 1 Aralık 2009 – 31 Aralık 2009.
- Bu seçili dönemde Ürün ID’si 9 ve 17 olan ürünler satılmış sadece. (Soldaki matris)
- Önceki yıla göre satışları bulmak istiyoruz, ama sadece Ürün ID’si 9 ve 17 olan ürünler için.
- Çünkü seçili tarih döneminde işlem gören ürünler sadece bu ikisi.
- 2008 yılı Aralık ayındaki satışlarda ise, başka ürünler de var. (Sağdaki matris)
Tipik satışlar ve önceki yıl aynı dönem metriğimizi yazalım ve soldaki matrise düşürelim:
Satışlar = SUM('Satışlar'[Tutar])
Satışlar ÖY =
CALCULATE([Satışlar] , DATEADD('Tarih'[Tarih], -1, YEAR ))
DATEADD, ya da SAMEPERIODLASTYEAR gibi fonksiyonlar ilgili tarih döneminde, yani 1-31 Aralık 2008’de ne görüyorsa onların toplamını gösteriyor. Bu toplam da 622.
Ama biz sadece -eğer satışı varsa- Ürün ID’si 9 ve 17 olan ürünlerin önceki yıl aynı dönem performansını bulmak istiyoruz! 9’un satışı olmamış, ama 17’nin satışı var! Yani soldaki matrise sadece 264 (Aralık 2008 satışı) gelsin istiyoruz.
Kafamızın çalışma şekline ve DAX tecrübemize göre birkaç farklı yöntemle çözebiliriz.
Önce nispeten biraz uzun bir mantık kuralım:
- Seçili tarih dönemi için “Ürünler” tablosunda filter context’te gördüğün ürün listesini bul
- Bu ürünlerden “Satışlar” tablosunda satışı olanları bul (satışı 0’dan büyük ürünler)
- Bu ürün listesi üzerinden önceki yıl aynı dönemi bul (DATEADD veya SAMEPERIODLASTYEAR ile)
Önce seçili dönemde satışı olan ürün listesini bulalım, hatta CONCATENATEX ile görelim değerleri!
Satışı Olan Ürünler =
VAR _GordugumuzUrunListesi =
ADDCOLUMNS(
VALUES('Ürünler'[Ürün ID]),
"@Satış" , CALCULATE([Satışlar])
)
VAR _SatisiOlanUrunler =
FILTER(
_GordugumuzUrunListesi,
[@Satış] > 0
)
RETURN
CONCATENATEX(_SatisiOlanUrunler, 'Ürünler'[Ürün ID] , "-" )
/* Ürünler tablosunda filter context'te gördüğün Ürün ID listesini
ve satışlarını bul, bu listeden satışı > 0 olanları bul
Bulduğun Ürün ID değerlerini araya - koyarak birleştir.*/
* ADDCOLUMNS bir iterator, [Satışlar] metriğinin başına CALCULATE yazsak da olur yazmasak da!
Bu listeyi, yani seçili dönemde hareket gören item’ları bulduktan sonra geriye kalan tek yapmamız gereken şey, önceki yıl aynı dönem formülüne bu listeyi CALCULATE ile birlikte bir filtre olarak vermek!
Yani:
Satışı Olan Ürünler ÖY =
VAR _GordugumuzUrunListesi =
ADDCOLUMNS(
VALUES('Ürünler'[Ürün ID]),
"@Satış" , CALCULATE([Satışlar])
)
VAR _SatisiOlanUrunler =
FILTER(
_GordugumuzUrunListesi,
[@Satış] > 0
)
RETURN
CALCULATE( [Satışlar ÖY], _SatisiOlanUrunler )
--CONCATENATEX(_SatisiOlanlanUrunler, 'Ürünler'[Ürün ID] , "-" )
İstediğimiz değeri bulduk! Satışlar ÖY değeri üzerinden örneklendirdim ama aynı mantığı her türlü zaman/periyod vs hesaplamasına gömebiliriz. Çalışacaktır.
Peki, nispeten uzun olan mantığı biraz daha basitleştirelim + performansını iyileştirelim!
Eğer modelimizdeki “Ürünler” tablosunda 8-10 bin ürün varsa yukarıdaki mantık gayet hızlı çalışır. Ama ya 1 milyon ürün varsa?
Performansı “olumlu” tetikleyecek başka bir mantık kuralım:
- Eğer bir Ürün satışlar tablosunda geçiyorsa, satışı var demektir!
- Her defasında Ürünler-Satışlar tabloları arasında satışı var mı diye kontrol etmek yerine, Satışlar tablosunu Ürün ID bazında gruplayalım!
- Önceki yıl aynı dönem hesabını bu liste üzerinden yapalım.
Satışı Olan Ürünler-2 =
VAR _SatisiOlanUrunler =
SUMMARIZE('Satışlar', 'Satışlar'[Ürün ID])
RETURN
CONCATENATEX(_SatisiOlanUrunler, 'Satışlar'[Ürün ID] , "-" )
Aynı listeyi bulduk! SUMMARIZE, bir nevi GROUP BY fonksiyonu!
Yukarıdaki aynı mantığı bu formüle de gömelim:
Satışı Olan Ürünler SUMMARIZE ile =
VAR _SatisiOlanUrunler =
SUMMARIZE('Satışlar', 'Satışlar'[Ürün ID])
RETURN
CALCULATE([Satışlar ÖY], _SatisiOlanUrunler )
Bu versiyon, ilk oluşturduğumuz mantığa göre -eğer Ürünler tablosunda yüzbinlerce ürün varsa- daha performanslı çalışacaktır: Çünkü Ürünler-Satışlar arasında git-gel yapmıyor, gereksiz iterasyona girmiyor! Ama ürünler tablosu küçükse aradaki farkı hissetmezsiniz büyük ihtimalle.
Uzun zamandır videolar yayınlıyordum, yazı yazmayı özlediğimi farkettim !
İyi bayramlar dilerim.
Yazıdaki modeli -bloga üyeyseniz- indirebilirsiniz.
Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!