Neredeyse 3 yıl önce hafta karşılaştırma ile ilgili bir DAX yazısı yazmıştım. Geçen hafta Twitter’da gördüğüm birkaç tweet üzerine, henüz güncel Power BI Desktop versiyonunda (Eylül 2022) intellisense’te çıkmasa da yeni OFFSET fonksiyonunu denemek istedim.
Haftalık karşılaştırma hesaplamalarını görece zorlaştıran şey, mevcut time intelligence grubu fonksiyonlarında bir hafta tanımı olmaması! Haftanın sabit bir tanımı yok çünkü, -misal- yılın ilk haftası hangi gün başlıyor? 1 Ocak mı, ilk Pazartesi mi, ilk Pazar mı? Hepsi olabilir!
O yazıda Tarih tablosundaki Hafta No sütunu üzerinden 17 satırda! yazdığım kodu, tarih tablosuna bir Hafta-İndex sütunu ekleyerek 8 satıra düşürebilmişim!
Henüz fonksiyonla ilgili bir dokümanstasyon olmadığı için deneme-yanılma yoluyla ve tweet’teki örnek üzerinden deneyelim bakalım!
OFFSET Fonksiyonu Syntax ve Parametreleri
OFFSET’in 3 parametresi var gözüküyor, ilki ileriye doğru mu yoksa geriye doğru mu “offset” yapacağız, ikincisi hangi sütun (tablo da olabilir, denemek lazım) üzerinden çalışacağız, üçüncüsü de sıralamayla ilgili. Bu son parametre opsiyonel gibi görünüyor, örneklerde kullandım ama kaldırdığımda da itiraz etmedi! (Muhtemelen ASC, DESC opsiyonları da vardır!)
…
Hafta Karşılaştırma Denemesi!
Yıl ve Hafta No sütunlarının olduğu bir matrise önceki hafta satışlarını getirmeye çalışalım. Örnek resimdeki gibi OFFSET içinde refere ettiğimiz sütunu ALL/ALLSELECTED parantezine almadan formülü yazmaya çalıştığımda patladı! İntellisense’te henüz olmadığı için kırmızı çizgiler olması normal ama alttaki hata mesajı bu formatı kabul etmediğini söylüyor!
Çıplak sütun referansını kabul etmemesinin sebeplerinden biri bu parametrenin bir tablo beklemesi olabilir, ya da bir iterator fonksiyon değil! VALUES ile denediğimde hata mesajı vermedi ama beklediğim sonucu da vermedi! Hafta No seviyesinde hiçbir şey göstermeyip yıl seviyesinde birşeyler göstermesi, RANKX’i metriklerde kullandığımızda karşımıza çıkan durumu hatırlattı.
Örnek resimdeki gibi sütunu ALL parantezine aldığımda mantıklı birşeyler göstermeye başladı!
Sonrasında ilk baktığım yer, yıl değiştiğinde ilk haftada ne gösterdiği oldu!
Birşey göstermiyor!
Daha önceki yazıda kullandığım mantıklardan birine benzer şekilde yılın son/ilk haftasıysa şöyle yap-böyle yap demek yerine Hafta İndex sütunu üzerinden bir mantık kuralım. Hafta Index sütunu, yıl değiştiğinde tekrar sıfırlanmayan, sürekli artan bir index sütunu. Nasıl eklediğimi ilgili yazıda okuyabilirsiniz.
Hafta Index Sütunu OFFSET =
CALCULATE(
[Satışlar],
OFFSET(
-1,
ALL('Tarih'[Hafta Index]),
ORDERBY('Tarih'[Hafta Index])
)
)
Bu metrik -kısmen- çalışıyor ama matriste Hafta İndex sütunu görünüyorsa!
Matriste “Hafta No” sütunu varken gösterdiği bu! Hafta seviyesinde birşey yok, yıl seviyesinde var!
Matrise “Hafta No” yerine “Hafta Index” sütunu seviyesinde baktığımızda çalışıyor ama yılın son-ilk haftası sorunu burada da var!
OFFSET içinde refere ettiğimiz sütun matriste gözüküyorsa çalışırım-yoksa çalışmam, ama yıl seviyesinde de birşeyler gösteririm durumu, yazdığımız son formüldeki context’i değiştirmemiz gerektiğini gösteriyor! Bütun yıllara bak, ya da tüm tarih tablosunu gör! Artı matriste index numarasını göstermek son kullanıcı açısından hoş olmayan bir görüntü! Hafta No mantıklı bir gösterim, hafta indexi rapora bakan için kafa karıştırıcı.
Hafta Index Sütunu OFFSET =
CALCULATE(
[Satışlar],
OFFSET(
-1,
ALL('Tarih'[Hafta Index]),
ORDERBY('Tarih'[Hafta Index])
),
ALL('Tarih')
)
Formüldeki context’i ALL(‘Tarih’) ile modifiye edince, matriste Hafta Index sütunu kullanmasak bile gayet güzel çalışıyor! Yılın son-ilk hafta problemi de yok!
Amacımız hafta hesaplama olduğu için bu metriği sadece hafta seviyesinde göstermek daha doğru. Bu yüzden formülü HASONEVALUE parantezine alıyorum.
Hafta Index Sütunu OFFSET =
IF( HASONEVALUE('Tarih'[Hafta No] ) ,
CALCULATE(
[Satışlar],
OFFSET(
-1,
ALL('Tarih'[Hafta Index]),
ORDERBY('Tarih'[Hafta Index])
),
ALL('Tarih')
)
)
3 yıl önceki yazıyı bitirdiğim son formülden daha uzun evet ama muhtemelen daha performanslı çalışıyordur. Bunu da başka bir zaman test edeceğim. Çünkü bu fonksiyonun asıl “heyecanlandırıcı” tarafı hafta no, index sütunu gibi bir mantık kurmanın daha zor olduğu durumlarda da çalışması, üstelik ekstra index gibi bir mantığa gerek kalmadan!
Kategori satışlarının olduğu bir matriste, bir önceki (ya da kaç öncesi/sonrası istiyorsak) satırda gözüken kategorinin satışlarını getirmek çok kolay değil DAX’ta.
Ama bu fonksiyonla mümkün:
Önceki Satırdaki Kategori Satışları =
CALCULATE(
[Satışlar],
OFFSET(
-1,
ALL('Ürün Kategorileri'[Kategori])
)
)
** Bu sefer ORDERBY kullanmadım, yani son parametre opsiyonel.
Bu matrisleri Excel’de 2 dakikada yapmak mümkün, DAX’ta değil! Hücre referansı yok DAX’ta, DAX öğrenmeye çalışan herkesin işini zorlaştıran da bu! Ama tasarım “by design” böyle! İyisi mi konseptleri öğrenelim, o zaman herşeyin bir çözümü oluyor.
Yazıdaki modeli indirebilirsiniz.