Exceldə təqvim cədvəlinin qurulması (Calendar Table)

posted in: Excel, Xəbər | 0
birbank biznes

Exceldə təqvim cədvəlinin qurulması (Calendar Table)

Təqvim cədvəlləri istənilən data modellərində fundamental əhəmiyyət daşıyır. Çünki dataların bir-biri ilə olan əlaqələrinin analizindən başqa, eyni datanın zaman içərisində necə dəyişdiyi də əhəmiyyətlidir. Bu səbəblə, hər data modelə aid düzgün Təqvim Cədvəlinin qurulması kritik önəmə sahibdir.
Qısaca, Təqvim Cədvəli nədir? – Data modeldə yer alan ən kiçik tarix ilə ən böyük tarix arasındakı hər günə aid tarixlərin sıralandığı bir sütundur. Bu sütundan daha sonra zamana aid bir çox yeni sütun əlavə edilməkdədir. Amma başlanğıc nöqtəsi hər zaman tək bir sütundur.

Burada kritik əhəmiyyət daşıyan nüans – bu diapazondakı tarixlərin kəsintisiz arxa-arxaya gəlmə zərurətidir. Arada unudulan heç bir tarix olmamalıdır (bu daha çox manual data əlavə edildiyi zaman rastlanan bir xətadır). Əks halda, data modelimiz işləməyəcəkdir.

Təqvim cədvəlini 4 fərqli yolla qura bilərik:

1. Excel-dən import edərək

Bu, məsələnin ən yalın və güvənilir olmayan bir həll yoludur. Çünki, zaman keçdikcə, tarixləri Excel-də manual olaraq yenilədikdən və yeni tarixləri əlavə etdikdən sonra, Query ilə Power BI-ya import etmək məcburiyyəti vardır. Və bu, hər yeniləmədə təkrarlanmaqdadır.

Tarixləri bu şəkildə alt-alta yazdıqdan sonra, Power BI daxilindəki Power Query vasitəsilə bu məlumatı çəkə bilərik. Lakin, Excel faylı o və ya bu səbəbdən silinsə, bu tarixlər olan cədvəl səhvliklə sətirlərini itirsə, o halda Power BI-dakı bütün data modelimiz çökəcəkdir. Bu səbəblə, bu yol çox da güvənilir bir yol deyil.

2. CALENDARAUTO funksiyası ilə

Birinci üsuldakı mənfi tərəfləri nəzərə alaraq, Power BI daxilində tamamilə dinamik bir təqvim hazırlamaq imkanımız vardır. Bu imkanın adı – CALENDARAUTO funksiyasıdır.

Menyudan Modeling >> New Table seçdikdən sonra, formul çubuğunda yeni cədvəlin Table= olaraq əmələ gəldiyini görürük.

Cədvəlimizin adını TəqvimCədvəli olaraq dəyişirik:

Funksiyanın cəmi 1 komponenti var: Maliyyə ilinin son ayının sıra nömrəsini müəyyən etmək. Yəni, ilimiz İyul-İyun dövrünə aiddirsə, o zaman 6 (yəni iyunun maliyyə ilinin son ayı olduğunu qeyd edirik), Yanvar-Dekabr aralığında isə 12 yazırıq.

Mən, maliyyə ilimizin ənənəvi Yanvar-Dekabr dövrünə təsadüf etdiyini fərz edir və həmin dövrün son ayı olan Dekabrın sıra nömrəsi olan 12-ni funksiya daxilinə əlavə edirəm. Enter düyməsinə basdıqdan sonra, modelimizdəki ilk tarixdən, ən son tarixə qədər olan bütün günlərin tarixləri alt-alta sıralanacaqdır. Hər hansı bir gün satış etməmiş olsanız belə, həmin tarix bu sıralamada öz yerini alacaqdır.

Bu üsulun mənfi tərəfi odur ki, məsələn, model daxilindəki fərqli bir cədvəldə işçilərə aid hər hansı bir sütunda, həmin işçilərin doğum tarixləri qeyd olunubsa (məsələn, doğum ili 1945 olan), o zaman CALENDARAUTO, o tarixi ən kiçik tarix olaraq qəbul edib, Təqvim Cədvəlimizi 1945-ci ildən etibarən formalaşdıracaqdır. Bu isə, əslində, tam istədiyimiz bir şey deyil. Bizə daha real tarixlər lazımdır. Belə olan halda isə, bu, bizi əsas həll yolu olan CALENDAR funksiyasına gətirəcəkdir.

3. CALENDAR funskiyası ilə – statik

Bu dəfə də menyudan Modeling >> New Table seçirik:

CALENDAR funksiyasını yazdıqdan sonra, 2 komponentdən ibarət olduğunu görəcəyik: Başlanğıc Tarixi və Bitiş Tarixi.

Bu tarixləri manual olaraq, yalın bir şəkildə yaza bilərik:

Görürük ki, CALENDAR funksiyasının daxilindəki komponentləri DATE funksiyası ilə əlavə etdik. DATE funksiyası çox sadə bir funksiya olmaqla bərabər, komponent olaraq, il nömrəsi, ay nömrəsi və gün nömrəsini almaqdadır. Formulu yazıb Enter düyməsinə basdıqdan sonra, yeni Təqvim Cədvəlimiz hazır olmuş olacaqdır.

Lakin, bu, statik bir cədvəldir. Son üsulda, bu cədvəli dinamik hala gətirəcəyik.

4. CALENDAR funksiyası ilə – dinamik

CALENDAR funksiyası ilə illəri 2018 və 2019 olaraq daxil etməyimizin ən böyük mənfi tərəfi odur ki, zaman keçdikcə, biz 2020-ci ilə gəldiyimizdə, funksiyanın içində Bitiş Tarixi olaraq 2019-cu il olduğuna görə, yeni ilə aid heç bir tarix nəzərə alınmayacaqdır. Nəzərə ala bilməsi üçün, funksiyaya daxil olub, ili bir daha dəyişməliyik. Bu cür manual işlərin qarşısını almaq üçün CALENDAR funksiyasındakı illəri aşağıdakı kimi dəyişməliyik:

İzlədiyimiz məntiq son dərəcə bəsitdir:

  1. Əsas datamız olan SalesData cədvəlindəki [Date] sütunundakı ən kiçik dəyərin ilini Başlanğıc Tarixinin ili,
  2. SalesData cədvəlindəki [Date] sütunundakı ən böyük dəyərin ilini isə Bitiş Tarixinin ili olaraq nəzərə alacaqdır.

Beləliklə, modelimizə yeni tarixlər daxil olduqca, hər dəfə model işə düşdükdə DAX motoru, tarixlərin ən böyüyünü tapacaq və onun ilini CALENDAR funksiyasındakı Bitiş Tarixinin ili olaraq əlavə edəcəkdir. Və hər şey bizim müdaxiləmiz olmadan, arxa fonda avtomatik qaydada baş verəcəkdir.

YEKUN – Dinamik CALENDAR funksiyasının digər 3 üsuldan üstünlükləri
  1. vs. Excel – Heç bir xarici təsirdən asılı olmadan, modelin daxilindəki tarixlər əsasında formalaşması
  2. vs. CALENDARAUTO – Sadəcə müəyyən edilən sütundakı tarixləri nəzərə alması
  3. vs. Statik CALENDAR – Başlanğıc və Bitiş tarixlərinin model tərəfindən müstəqil şəkildə yenilənməsi.
Müəllif: Elçin Xəlilov

Print Friendly, PDF & Email