Statik Gruplama, Fatura Yaşlandırma Örneği

Power BI ‘da hangi konuyla ilgili model kurarsak kuralım ihtiyaç duyduğumuz şeylerden biri de modeldeki varlıkları -ürün, kategori, renk, hesap kodu vs- nümerik değerlerine göre gruplara ayırmak, bir nevi segmentasyon yapmak. Gruplama İngilizce jargonda banding diye de geçiyor. Fatura yaşlandırma bu gruplama ihtiyacının en çok lazım olduğu konulardan biri.

Temel olarak iki farklı gruplama şekli oluşturabiliriz: Statik, dinamik. Statik gruplama -adı üzerinde- kullanıcının rapor ekranında seçtiği filtrelere tepki vermez. Örneğin müşterileri yaş gruplarına göre sınıfladığımızda X müşteri gençse hep gençtir, 30-40 yaş aralığındaysa gene hep o aralıktadır. Ta ki verileri tamamen yeniden güncelleyinceye kadar bu özelliğini kaybetmez ve sabit kalır.

Dinamik gruplama ise -gene adı üzerinde- değişkendir. Ürün veya müşterileri satış büyüklükleri üzerinden  ABC sınıflamasına göre ayırsak, X müşteri veya ürün bu sene A sınıfındayken önceki sene C sınıfında olabilir. Yani bu gruplama kullanıcının rapor ekranında yapacağı seçimlere göre değişebilir, sabit değildir.

Hazır bu iki farklı gruplamayı tanımlamışken sık sorulan -ve bazen tek bir doğru cevabın olmadığı- “Ne zaman hesaplanmış sütun ne zaman metrik kullanalım” sorusuna da değinelim: Müşterinin yaşına göre statik bir gruplama yapacaksak bize hesaplanmış bir sütun lazım. Ya da bu daha doğru bir seçim olur. Hangi gruba düşüyorsa o grubun değerini müşteriler tablosunda bir sütun olarak görmemiz lazım.

Eğer müşterileri satış büyüklüklerine göre dinamik olarak sınıflandıracaksak bunu hesaplanmış sütun olarak yapamayız! Bunu ancak metrik yazarak yapabiliriz.

Hesaplanmış sütun okuduğumuz tablodaki diğer tüm orijinal sütunlar gibidir. Orijinal sütunlardan tek farkı DAX ile hesaplanmış olmasıdır. Veri bir dahaki sefere güncellenene dek sabittir, değişmez. Metrik ise sabit değildir, her seferinde hangi filter context altında çalışıyorsa buna göre hesaplanır.

Bu sabit gruplamayı en çok kullandığımız yerlerden biri de fatura yaşlandırma. Alacaklarımızı – ve borçlarımızı – vade veya termin tarihlerine göre gruplamak gerekiyor.

** Alacak ve borçların vadelerine göre karşılaştırılması her şirket için istisnasız gözetilen şeylerden biridir. Alacaklarınızı 30-60 günde tahsil ediyor borçlarınızı 60-90 günde ödüyorsanız işler yolundadır!  Tabii büyüklükleri de orantılılıysa. Durum tersiyse aksiyon almanızı gerektirecek “şeyler” var demektir.

Örnek veri seti aşağıdaki gibi:

Tabloda hareketin vade tarihi  ve bir de ilgili hareketin borç mu yoksa alacak mı olduğunu gösteren bir başka sütun var.

…

Power BI’ da her şey DAX değil elbette, Power Query son derece kabiliyetli bir ETL aracı. Hatta dataflow’larla birlikte müthiş bir araca dönüştü. Yaşlandırma için gruplama işini DAX tarafında da yapabiliriz, Power Query tarafında da.

Power Query ile Statik Gruplama

Önce Power Query örneğini yapalım. PQ tarafında tabloya iki tane sütun ekliyorum. İlki mevcut bugünün (yani aslında verinin güncellendiği günün) tarihiyle vade tarihi arasındaki farkı bulacak. Bu ilk sütunu “custom column” olarak ekliyorum.

PQ Gün Farkı = Date.From (DateTime.LocalNow()) - [Vade Tarihi]

DateTime.LocalNow() verinin güncellendiği anı tarih ve saat bilgisiyle birlikte bulacak, en dıştaki Date.From ise bunun sadece tarih kısmını almak için! Yeni oluşan sütunun veri tipini de tam sayı olarak değiştirdim!

İkinci sütunu “conditional column” olarak ekliyorum. Gün farkına göre gruplama yapacak!

Neredeyse tüm programlama dillerindeki IF kalıplarında olduğu gibi, condition’ların sıralaması önemli. Çünkü ilk “True” dönen condition sonrasında execution stop eder. Yani diğer condition’lara bakılmaz, kodun çalışması durur. Bu yüzden en küçük gün farkından başladık.

Verileri aktardıktan sonra tutar sütunu üzerinden toplam alan bir metrikle birlikte aşağıdaki gibi görseller oluşturabiliriz.

Toplam Tutar := SUM( 'Data'[Tutar] )

Hesaplanmış Sütun ile Statik Gruplama

Aynı örneği bu sefer DAX ile yapalım. Mantık gene aynı sadece kullanacağımız fonksiyonlar farklı olacak. Bugün ile vade tarihi arasındaki gün farkı için aşağıdaki formüllerden herhangi birini kullanabiliriz, hepsi aynı sonucu verecektir.

Gün Farkı = (TODAY() - 'Data'[Vade Tarihi] ) * 1

Gün Farkı INT_ = INT(TODAY() - Data[Vade Tarihi] )

Gün Farkı DATEDIFF_ = DATEDIFF( Data[Vade Tarihi] ; TODAY() ; DAY )

** İlk ve ikinci formülleri * 1 ve INT parantezi olmadan yazdığımızda ne gösterdiğine bakmanızı öneririm!

DATEDIFF biraz daha kabiliyetli bir fonksiyon: Eğer iki tarih arasındaki farkı sadece gün sayısı olarak değil saat/dakika/ay vs.. farkı olarak bulmak isterseniz son parametresinde bunu belirtebiliyorsunuz.

Devamında bize gene bir gruplama sütunu lazım olacak. Aynı PQ tarafındaki örneğe benzer şekilde koşullu bir formül yazabiliriz.

Yaşlandırma = 
-- IF( 'Data'[Gün Farkı] <= 0 ; "Vadesi Gelmemiş" ;
--     IF('Data'[Gün Farkı] <= 30 ; "1-30 Gün" ;
--         IF( 'Data'[Gün Farkı] <= 60 ; "31-60 Gün" ;
--             IF( 'Data'[Gün Farkı] <= 90 ; "61-90 Gün" ; 
--             ">+90" )
--         )
--     )
-- )

SWITCH( 
    TRUE() ,
    Data[Gün Farkı] <= 0 , "Vadesi Gelmemiş" ,
    Data[Gün Farkı] <= 30 , "1-30 Gün" ,
    Data[Gün Farkı] <= 60 , "31-60 Gün" ,
    Data[Gün Farkı] <= 90 , "61-90 Gün" , 
    ">+90"
)

İçiçe (nested) IF’le yazmak nispeten zor ama kod yazmaya alışmak açısından da güzel bir pratik! Hangi IF’in hangi koşulunu yazıyorum derken biraz acı çekmekte -uzun kodları yazmaya alışmak açısından- fayda var! Sonrasında SWITCH daha tercih edilir bir fonksiyon, daha temiz, daha kolay.

PQ ile oluşturduğumuz çözümün aynısını DAX ile yapmış olduk.

Değişken yapısını kullanarak ekstra bir “Gün Farkı” sütunu yaratmadan da yaşlandırma grubunu bulabiliriz aslında! Ne kadar az sütun o kadar iyi!

Yaşlandırma Değişken = 
VAR _GunFarki = ( TODAY() - 'Data'[Vade Tarihi] ) * 1
VAR _YaslandirmaGrubu =
    SWITCH( 
        TRUE() ,
        _GunFarki <= 0 , "Vadesi Gelmemiş" ,
        _GunFarki <= 30 , "1-30 Gün" ,
        _GunFarki <= 60 , "31-60 Gün" ,
        _GunFarki <= 90 , "61-90 Gün" , 
        ">+90"
    )
RETURN 
_YaslandirmaGrubu

Gruplamayla ilgili daha önce yazdığım bir başka yazının linkini de buraya ekliyorum. Konu birebir aynı olmasa bile yakın, bakmak isteyebilirsiniz.

Yukarıdaki çözümlerin birkaç tane kozmetik durumu ya da eksiği var!

İlki gruplarla ilgili sıralama! “>+90” sıralamada en üstte çıkıyor harf sırasına göre. Belki de en sonda olmasını istiyoruz!  “Vadesi Gelmemiş” ‘leri en başta görmek istiyoruz! Bunun için “Sort By Column” ‘u kullanabiliriz.

İkincisi ise aralık değerlerini koda gömmüş olmamız! Eğer aralıklar sabitse ve değişmeyecekse bu yazdığımız örneklerde bir sorun yok. Ama vade grupları değişebilecekse bunları koda gömmek yerine “kullanıcının değiştirebileceği” bir tabloya gömmek daha kullanışlı bir yöntem olabilir.

Tablonun görünümü şunun gibi olabilir!

Hareketlerin olduğu tablo ile bu tablo arasında herhangi bir ilişki yok, ama aşağıdaki gibi bir hesaplanmış sütun formülüyle hareket tablosundaki her bir kaydın hangi aralığa düştüğünü bulabiliriz.

Yaşlandırma Grubu = 
CALCULATE( 
    VALUES( 'Yaslandırma Grupları'[Grup] ) ,
    FILTER( 'Yaslandırma Grupları' ,
        'Data'[Gün Farkı] > 'Yaslandırma Grupları'[Alt Sınır] &&
        'Data'[Gün Farkı] <= 'Yaslandırma Grupları'[Üst Sınır]
    )
)

Yukarıdaki formülde VALUES yerine MIN veya MAX da kullanabiliriz. Her bir hareketin sadece ve sadece tek bir gruba düştüğünü biliyoruz çünkü. Eğer grupların olduğu tabloda alt sınır-üst sınır değerleri arasında boşluklar varsa VALUES hata verebilir! VALUES bir tablo fonksiyonu olmasına rağmen dönen tabloda tek bir satır-sütun varsa bunu otomatik olarak bir değere çevirir, ama birden fazla satır-sütun dönerse bunu tek bir değere çeviremeyeceğinden hata verecektir.

Bu oluşturduğumuz sütunu kullanarak da aynı görselleri elde edebiliriz.

Sıralama işini de düzeltelim. Yani “Vadesi Gelmemiş” ‘ler ilk sırada “>+90” ‘lar sonda çıksın. Çözüm çok basit gözüküyor! Grupların değerine göre 1,2,… değerlerini alacak yeni bir sıralama sütunu oluşturup, “Yaşlandırma Grubu” sütununu da buna göre “sort by” etmek!

Sıralama = 
SWITCH( 
    TRUE() ,
    'Data'[Yaşlandırma Grubu] = "Vadesi Gelmemiş" , 1 ,
    'Data'[Yaşlandırma Grubu] = "1-30 Gün" , 2 ,
    'Data'[Yaşlandırma Grubu] = "31-60 Gün" , 3 ,
    'Data'[Yaşlandırma Grubu] = "61-90 Gün" , 4 ,
    5
)

Her şey normal gözüküyorken “Yaşlandırma Grubu” sütununu bu “Sıralama” sütununa göre sıralamak istediğimizde hata mesajı veriyor!

“Circular dependency” hatası ! Birbirine bağlı, birbirini refere eden hesaplanmış sütunlar oluştururken karşımıza çıkma ihtimali en yüksek hata mesajı!

Bu konuyu başka bir yazıya bırakayım, asıl konuyu bitirelim. Ama notumuzu da düşmüş olalım: Birbirini refere eden hesaplanmış sütunları hangi sırada ve hangi fonksiyonlarla refere ettiğimiz bazen önemlidir. Hesaplama sırasını bulamadığı zaman DAX buna benzer bir “dependency” hatası verebilir.

Sıralama sütünu için yazdığım formülü aşağıdaki gibi değiştiriyorum ve “Yaşlandırma Grubu” sütununu bu yeni sıralama sütununa göre sıralıyorum.

Sıralama -2 = 
SWITCH( 
    TRUE() ,
    Data[Gün Farkı] <= 0 , 1 ,
    Data[Gün Farkı] <= 30 , 2 ,
    Data[Gün Farkı] <= 60 , 3 ,
    Data[Gün Farkı] <= 90 , 4 , 
    5
)

Hata mesajı almaksızın istediğimiz sıralamayı da elde ettik!

Herhangi bir şeyi yapmanın çoğunlukla birden fazla yolu var Power BI’da. DAX da Power Query de bir nevi alet edavat çantası gibi. Nasıl kullanacağımız biraz bize bağlı, tecrübemize bağlı, kafamızın nasıl çalıştığına bağlı, “dependency” hatasında olduğu gibi bazen uygulamanın tasarlanma şekli bile çözümü belirleyebiliyor.

Yazıdaki modeli -bloga üyeyseniz- indirebilirsiniz.

Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!