Tüm Power BI modellerinde, modelinizin kapsamı ne olursa olsun, mutlaka olması gereken bir tablo var: Tarih tablosu!
Hangi iş zekası uygulamasını kullanırsanız kullanın, hepsinde ortak olan bir metrik grubu var; zaman karşılaştırmalarıyla ilgili metrikler. Yani “Satışlar Önceki Ay”, “Satışlar Geçen Sene Aynı Dönem”, “Satışlar Önceki Yıl”, “Önceki Yıla Göre Artış” gibi, bir önceki aya göre, bir önceki periyoda vs. göre zaman dilimlerini karşılatırmamızı sağlayan metrikler.
DAX’ta tarihle ilgili işlemler ve hesaplamalar yapmamızı sağlayan iki farklı kategori var; birincisi “tarih ve zaman fonksiyonları” (date-time functions), ikincisi “time intelligence” fonksiyonları.
Tarih-zaman fonksiyonları genel olarak, “date” veri tipindeki bir sütunla ilgili tarih ve zaman bilgilerini döndürür. (Ne kadar açıklayıcı bir cümle!). Örneğin; Today(), bugünün tarihini döndürür. Month (Today()); bugünün ait olduğu ayı döndürür.
Time Intelligence fonksiyonları ise, bir metriğin farklı zaman periyotlarındaki değerlerini bulmamızı, dolayısıyla zaman periyodu bazında metrik değerlerini karşılaştırabilmemizi sağlar. Örneğin satışların önceki aya göre artışını hesaplamak istiyorsak, [Satışlar] metriğinin bir ay önceki değerini gösterecek bir başka metriği hesaplayabilmemiz lazım ki, ikisinin farkını artış olarak gösterebilelim.
Önce Power BI’ın yerleşik olarak zaman ve tarihle ilgili ne sağladığından başlayalım:
Örnek modelde sadece bir tane tablomuz var: Satışlar tablosu. Satışlar tablosunda, veri tipi “date” olan, gün-ay-yıl formatında bir Tarih sütunu var. Tutar sütununu toplayan basit bir de “Satışlar” metriğimiz var.
Satışlar metriğini, Tarih sütunuyla birlikte sütun grafiğine ve matrise düşürdüğümüzde, modelde “Year”, “Quarter”, “Month”, “Day” gibi yıl-çeyrek-ay bilgilerini gösteren sütunlar olmamasına rağmen, görsellerde olduğunu görüyoruz. Aynı zamanda görsele düşürdüğümüz “Tarih” sütununun altına “Year-Quarter-Month-Day” hiyerarşisi eklendi. Oysa sadece “Tarih” sütununu eklemiştik.
Bunun sebebi, Power BI’ın yerleşik olarak (built-in) bir Tarih tablosuna sahip olmasıdır. DAX Studio ile modele bağlandığımızda bu tabloyu ve yapısını görebiliriz.
Eğer modelinizde ayrı bir “Tarih” tablosu yoksa, tarih tipindeki sütunlar üzerinden yıl-çeyrek-ay bazında görseller oluşturmak için kullanabileceğiniz bir özellik. PBI’ın bu “built-in” tarih tablosu özelliğini ayarlardan (Files –> Options and Settings –>Options –> Data Load) açabilir ya da kapatabilirsiniz.
Güzel bir özellik gibi dursa da, ben size bu özelliği her zaman kapalı tutmanızı ve ayrı bir Tarih tablosu kullanmanızı önereceğim.
İki sebepten dolayı:
- Time intelligence fonksiyonlarının doğru çalışabilmesi için bir Tarih tablosundan, her bir günün tarihini gösteren bir Tarih sütununu çağırmamız gerekiyor. Oysa bu yerleşik tablonun adının ne olduğunu bile bilmiyoruz!
- Time intelligence fonksiyonları için ayrı bir Tarih tablosu oluşturduğumuzda bu özelliği kapatmaz isek, yerleşik “görünmez” “internal” bir tablo olarak modelde yer işgal edeceğinden dosya büyüklüğünü arttırır.
Kısaca, bu özelliği ayarlardan her zaman kapatın ve kendi tarih tablonuzu oluşturun.
Yazının ana konusuna nihayet geldik: Tarih tablosunu nasıl oluşturacağız?
Üç farklı yöntem kullanabiliriz:
- CALENDAR(), CALENDARAUTO() gibi DAX komutlarını kullanabiliriz. Yazıyı çok uzatmamak adına bu komutların kullanımına çok girmeyeceğim ama, CalendarAuto(), modelinizdeki tüm tablolardaki veri tipi “date” olan sütunlara bakar ve bu sütunlardaki en küçük ve en büyük tarihe göre ardışık olarak tüm gün tarihlerini içeren tek sütunluk bir Tarih tablosu oluşturur.
- PowerQuery ile -yani M dili ile- kendi Tarih tablomuzu oluşturabiliriz.
- Önceden hazırlanmış Excel, Access veya SQL tablosu formatında bir Tarih tablosunu kullanabiliriz.
Üçüncü opsiyonu daha çok tercih ediyorum: çünkü özellikle belli tarih aralıklarının hangi dönemlere ait olduğunu daha kolay belirleyebiliyorum. Örneğin mağazacısınız ve anneler günü kampanyasında yaptığınız satışları diğer dönemlerle karşılaştıracaksınız: Anneler günü satışlarını hangi dönem aralığı olarak kabul edeceksiniz? Anneler gününden önceki 7 günü mü? Yoksa 10 günü mü? Önceden hazırlanmış bir Tarih tablosu ile bunu kendiniz dilediğiniz gibi belirleyebilirsiniz.
Bir Tarih tablosunda bize lazım olabilecek sütunlar kabaca ekteki gibidir:
Kampanya dönemleri gibi şirkete özel olabilecek durumlar için biraz daha farklı -yarı manuel oluşturulmuş- Tarih tabloları da kullanılabilir.
Power BI (pbix) ve Excel 2016 formatındaki “Tarih” tablolarını ekteki linklerde paylaşıyorum. Excel dosyası 2013-2020 yılları arası tüm tarihleri içeren statik bir tablo. PBI dosyası ise “kendi tarih aralığınızı” vererek kendi Tarih tablonuzu oluşturmanıza müsade eder.
PBI dosyasının PowerQuery tarafını açıp dilediğiniz başlangıç ve bitiş tarihlerini girip -mali yıl başlangıç ayıyla birlikte- istediğiniz aralıkta bir Tarih tablosu oluşturabilirsiniz.
Ayrı bir “Tarih” tablomuz da artık olduğuna göre, özellikle “time intelligence” fonksiyonlarını kullanırken, Tarih tablosu üzerinde işlem yaparken uymamız gereken “altın” kuralları sıralayabiliriz:
- Tarih tablosu “gün” seviyesinde (granularity) olmalıdır. Ardışık tüm günleri içermelidir ve arada hiç bir boşluk olmamalıdır.
- Yıl (lar) seviyesinde bir tablo tanımlıyorsak, ilgili yıllara ait tüm günler tarih tablosunda olmalıdır. İlgili yıllara ait 365 günün tamamı olmalıdır.
- Time intelligence fonksiyonlarını kullanırken asla ve asla transaction tablolarındaki tarih sütunlarını çağırmayın. Her zaman Tarih tablosunundaki gerekli sütunları çağırın.
- Transaction tabloları ve Tarih tablosu arasında mümkün mertebe veri tipi “date” olan sütunlar arasında ilişkiler tanımlayın. Yapay, kompozit anahtar sütunları kullanmayın.
Linklerde verilen tarih şablonları ilk iki kuralı sağlar, diğer ikisine dikkat etmenizi şiddetle öneririm.
Yukarıdaki kurallara uyan “kusursuz” bir tarih tablosu, her veri modelinin olmazsa olmazıdır.
Çok yararlı bir yazı ve kaynak olmuş. Teşekkürler. Finansal takvim kullananlar için ben de PBI tarih template paylaşabilirim.
Buyrun paylaşın. Memnun oluruz.
Yardımcı olur mu bilmiyorum ama ben şu şekilde hazırlıyorum tablomu arkadaşlar.
1)Kendime sql ile DimZaman tablosu oluşturuyorum.
2)Kendi işlem tablolarımdaki tarihleri de bu tablo içinde bulunan “zamanId” formatına çevirip, aralarında ilişki kuruyorum.
Örneğin;
Tablonuzdaki faturaTarihi alanının formatını değiştirerek aşağıdaki gibi yeni bir sütuna yazın. (DimZamanId sütunu)
==> CONVERT(VARCHAR(112), faturaTarihi , 112) <==
Böylece işlem tablosundaki zamanId ile Dim.Zaman tablosunu eşleştirebilirsiniz.
(Dim.Zaman tablosunu oluşturan sorguyu aşağıda paylaştım)
Function : İki tarih arasını bize verecek olan fonksiyonumuz.
***************************************************************
CREATE FUNCTION [Sistem].[ufn_GetDateList]
(
@pTarihBaslangic DATETIME ,
@pTarihBitis DATETIME
)
RETURNS TABLE
AS
RETURN
(
WITH T ( date )
AS ( SELECT @pTarihBaslangic
UNION ALL
SELECT DATEADD(DAY, 1, T.date)
FROM T
WHERE T.date < @pTarihBitis
)
SELECT CONVERT(DATE, date) Tarih
FROM T
)
******************************************************************
Daha sonra aşağıdaki sorgu ile tabloyu oluşturuyorum; Dili Türkçe yapmazsak gün ve ay isimleri ingilizce olacaktır.
SET LANGUAGE Turkish;
INSERT INTO [DIM].[Zaman]
( [Id] ,
Zaman ,
[Yil] ,
[AyId] ,
[AyAdi] ,
[GunId] ,
[GunAdi] ,
[AyHaftaId] ,
[YilHaftaId]
)
SELECT CONVERT(VARCHAR(112), Tarih, 112) Id ,
Tarih ,
DATEPART(YEAR, Tarih) Yil ,
DATEPART(MONTH, Tarih) AyId ,
UPPER(DATENAME(MONTH, Tarih)) AyAdi ,
UPPER(DATENAME(DAY, Tarih)) GunId ,
UPPER(DATENAME(dw, Tarih)) GunAdi ,
CASE WHEN DATEPART(DAY, Tarih) <= 7 THEN 1
WHEN DATEPART(DAY, Tarih) <= 14 THEN 2
WHEN DATEPART(DAY, Tarih) <= 21 THEN 3
WHEN DATEPART(DAY, Tarih) <= 28 THEN 4
ELSE 5
END AyHaftaId ,
DATEPART(wk, Tarih) YilHaftaId
FROM [Sistem].[ufn_GetDateList](༽.01.2017', ༽.01.2025')
OPTION ( MAXRECURSION 0 )
**********************************************************************
Son olarak işlem tablolarınızı Power BI Desktop içerisine attıktan sonra, tabloları zamanId alanından birbiriyle ilişkilendirebilirsiniz.
Ben tarih tablosu oluşturmak için Transform Data alanına geliyorum.Oradan New Source seçip daha sonra Blank Query seçiyorum daha sonra Advanced Editor a tıklıyorum açılan alana aşağıdaki kodu yapıştırıyorum.
let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate – StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, “YearNumber”, each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, “QuarterOfYear”, each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, “MonthOfYear”, each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, “DayOfMonth”, each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, “DateInt”, each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, “MonthName”, each Date.ToText([Date], “MMMM”), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, “MonthInCalendar”, each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & ” ” & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, “QuarterInCalendar”, each “Q” & Number.ToText([QuarterOfYear]) & ” ” & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, “DayInWeek”, each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, “DayOfWeekName”, each Date.ToText([Date], “dddd”), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, “WeekEnding”, each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, “Week Number”, each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,”MonthnYear”, each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,”QuarternYear”, each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{“QuarternYear”, Int64.Type},{“Week Number”, Int64.Type},{“Year”, type text},{“MonthnYear”, Int64.Type}, {“DateInt”, Int64.Type}, {“DayOfMonth”, Int64.Type}, {“MonthOfYear”, Int64.Type}, {“QuarterOfYear”, Int64.Type}, {“MonthInCalendar”, type text}, {“QuarterInCalendar”, type text}, {“DayInWeek”, Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, “ShortYear”, each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, “FY”, each “FY”&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY
in
fnDateTable