Tabel tanggal di Power Pivot sangat penting untuk menelusuri dan menghitung data seiring waktu. Artikel ini menyediakan pemahaman menyeluruh tentang tabel tanggal dan cara membuatnya di Power Pivot. Secara khusus, artikel ini menguraikan:
-
Mengapa tabel tanggal penting untuk menelusuri dan menghitung data menurut tanggal dan waktu.
-
Cara menggunakan Power Pivot untuk menambahkan tabel tanggal ke Model Data.
-
Cara membuat kolom tanggal baru seperti Tahun, Bulan, dan Periode dalam tabel tanggal.
-
Cara membuat hubungan antara tabel tanggal dan tabel fakta.
-
Cara bekerja dengan waktu.
Artikel ini ditujukan bagi pengguna yang baru menggunakan Power Pivot. Namun, penting bagi Anda untuk sudah memiliki pemahaman yang baik tentang mengimpor data, membuat hubungan, dan membuat kolom dan pengukuran terhitung.
Artikel ini tidak menjelaskan cara menggunakan fungsi DAX Time-Intelligence dalam mengukur rumus. Untuk informasi selengkapnya tentang cara membuat pengukuran dengan fungsi Inteligensi Waktu DAX, lihat Inteligensi Waktu di Power Pivot Excel.
Catatan:Â Di Power Pivot, nama "ukur" dan "bidang terhitung" sinonim. Kami menggunakan ukuran nama di seluruh artikel ini. Untuk informasi selengkapnya, lihat Pengukuran di Power Pivot.
Konten
Memahami tabel tanggal
Hampir semua analisis data melibatkan penelusuran dan membandingkan data selama tanggal dan waktu. Misalnya, Anda mungkin ingin menjumlahkan jumlah penjualan untuk kuartal fiskal sebelumnya, lalu membandingkan total tersebut dengan kuartal lain, atau Anda mungkin ingin menghitung saldo penutupan akhir bulan untuk sebuah akun. Dalam setiap kasus ini, Anda menggunakan tanggal sebagai cara untuk mengelompokkan dan mengagregasi transaksi penjualan atau saldo untuk periode waktu tertentu.
Laporan Power View
Tabel tanggal bisa berisi banyak representasi tanggal dan waktu yang berbeda. Misalnya, tabel tanggal akan sering memiliki kolom seperti Tahun Fiskal, Bulan, Kuartal, atau Periode yang bisa Anda pilih sebagai bidang dari Daftar Bidang saat lisensi dan pemfilteran data Anda di PivotTable atau laporan Power View.
Daftar Bidang Power View
Untuk kolom tanggal seperti Tahun, Bulan, dan Kuartal untuk menyertakan semua tanggal di dalam rentangnya masing-masing, tabel tanggal harus memiliki setidaknya satu kolom dengan kumpulan tanggal yang bersebelahan. Artinya, kolom tersebut harus memiliki satu baris untuk setiap hari untuk setiap tahun yang disertakan dalam tabel tanggal.
Misalnya, jika data yang ingin Anda telusuri memiliki tanggal dari 1 Februari 2010 hingga 30 November 2012, dan Anda melaporkan pada tahun kalender, maka Anda akan menginginkan tabel tanggal dengan setidaknya rentang tanggal dari 1 Januari 2010 hingga 31 Desember 2012. Setiap tahun dalam tabel tanggal Anda harus berisi semua hari untuk setiap tahun. Jika Anda akan secara berkala melakukan refresh data Anda dengan data yang lebih baru, ada baiknya Anda menjalankan tanggal berakhirnya satu atau dua tahun, sehingga Anda tidak harus memperbarui tabel tanggal Anda sesuai waktu yang anda lewati.
Tabel tanggal dengan kumpulan tanggal yang bersebelahan
Jika Anda melaporkan pada tahun fiskal, Anda bisa membuat tabel tanggal dengan serangkaian tanggal yang bersebelahan untuk setiap tahun fiskal. Misalnya, jika tahun fiskal Dimulai pada tanggal 1 Maret, dan Anda memiliki data untuk tahun fiskal 2010 hingga tanggal saat ini (misalnya, di FY 2013), Anda bisa membuat tabel tanggal yang dimulai pada 3/1/2009 dan menyertakan setidaknya setiap hari dalam setiap tahun fiskal hingga tanggal terakhir dalam Tahun Fiskal 2013.
Jika Anda akan melaporkan pada tahun kalender dan tahun fiskal, Anda tidak perlu membuat tabel tanggal terpisah. Tabel tanggal tunggal bisa menyertakan kolom untuk tahun kalender, tahun fiskal, dan bahkan tiga belas kalender periode empat minggu. Hal yang penting adalah tabel tanggal Anda berisi kumpulan tanggal yang bersebelahan untuk semua tahun yang disertakan.
Menambahkan tabel tanggal ke Model Data
Ada beberapa cara untuk menambahkan tabel tanggal ke Model Data Anda:
-
Mengimpor dari database relasional, atau sumber data lainnya.
-
Membuat tabel tanggal di Excel lalu menyalin atau menautkan ke tabel baru di Power Pivot.
-
Mengimpor dari Microsoft Azure Marketplace.
Mari kita lihat masing-masing hal ini lebih dekat.
Mengimpor dari database relasional
Jika Anda mengimpor beberapa atau semua data dari gudang data atau tipe database relasional lainnya, kemungkinan sudah ada tabel tanggal dan hubungan antara data tersebut dan data lainnya yang Anda impor. Tanggal dan format kemungkinan akan cocok dengan tanggal di data fakta Anda, dan tanggal mungkin dimulai dengan baik di masa lalu dan akan jauh ke masa mendatang. Tabel tanggal yang ingin Anda impor mungkin sangat besar dan berisi rentang tanggal di luar yang perlu Disertakan dalam Model Data Anda. Anda dapat menggunakan fitur filter tingkat lanjut Panduan Impor Tabel Power Pivot untuk secara selektif memilih tanggal dan kolom tertentu yang benar-benar Anda perlukan. Hal ini dapat sangat mengurangi ukuran buku kerja Anda dan meningkatkan kinerja.
Panduan Impor Tabel
Di sebagian besar kasus, Anda tidak perlu membuat kolom tambahan seperti Tahun Fiskal, Minggu, Nama Bulan, dll. karena kolom baru akan ada dalam tabel yang diimpor. Namun, dalam beberapa kasus, setelah Tabel tanggal diimpor ke Model Data, Anda mungkin perlu membuat kolom tanggal tambahan, bergantung pada kebutuhan pelaporan tertentu. Untungnya, hal ini mudah dilakukan menggunakan DAX. Anda akan mempelajari selengkapnya tentang membuat bidang tabel tanggal nanti. Setiap lingkungan berbeda. Jika anda tidak yakin apakah sumber data Anda memiliki tanggal atau tabel kalender terkait, bicarakan dengan administrator database Anda.
Membuat tabel tanggal di Excel
Anda bisa membuat tabel tanggal di Excel lalu menyalinnya ke tabel baru dalam Model Data. Ini benar-benar sangat mudah dilakukan dan memberi Anda banyak fleksibilitas.
Saat Anda membuat tabel tanggal di Excel, Anda mulai dengan satu kolom dengan rentang tanggal yang berdekatan. Selanjutnya Anda bisa membuat kolom tambahan seperti Tahun, Kuartal, Bulan, Tahun Fiskal, Periode, dll. di lembar kerja Excel dengan menggunakan rumus Excel, atau, setelah Anda menyalin tabel ke dalam Model Data, Anda bisa membuatnya sebagai kolom terhitung. Membuat kolom tanggal tambahan di Power Pivot dijelaskan di bagian Menambahkan Kolom Tanggal Baru ke Tabel Tanggal selanjutnya di artikel ini.
Caranya: Membuat tabel tanggal di Excel dan menyalinnya ke dalam Model Data
-
Dalam Excel, dalam lembar kerja kosong, di sel A1, ketikkan nama header kolom untuk mengidentifikasi rentang tanggal. Biasanya, ini akan menjadi sesuatu seperti Date, DateTime, atau DateKey.
-
Di sel A2, ketikkan tanggal awal. Misalnya, 1/1/2010.
-
Klik gagang isian dan seret ke bawah ke nomor baris yang menyertakan tanggal selesai. Misalnya, 31/12/2016.
-
Memilih semua baris dalam kolom Tanggal (termasuk nama header di sel A1).
-
Dalam grup Gaya, klik Format sebagai Tabel, lalu pilih gaya.
-
Dalam kotak dialog Format sebagai Tabel, klik OK.
-
Salin semua baris, termasuk header.
-
Di Power Pivot, pada tab Beranda, klik Tempel.
-
Dalam Pratinjau >Ketik Nama Tabel ketik nama seperti Tanggal atau Kalender. Biarkan Gunakan baris pertama sebagai header kolom dicentang, lalu klik OK.
Tabel tanggal baru (bernama Kalender dalam contoh ini) di Power Pivot terlihat seperti ini:
Catatan:Â Anda juga bisa membuat tabel tertaut dengan menggunakan Tambahkan ke Model Data. Namun, ini membuat buku kerja Anda tidak terlalu besar karena buku kerja memiliki dua versi tabel tanggal; satu di Excel dan satu di Power Pivot..
Catatan:Â Tanggal nama adalah kata kunci di Power Pivot. Jika Anda memberi nama tabel yang Anda buat dalam Tanggal Power Pivot, maka Anda harus menyertakan nama tabel dengan tanda kutip tunggal dalam rumus DAX apa pun yang mereferensikannya dalam argumen. Semua contoh gambar dan rumus dalam artikel ini merujuk ke tabel tanggal yang dibuat di Power Pivot bernama Kalender.
Sekarang Anda memiliki tabel tanggal dalam Model Data Anda. Anda dapat menambahkan kolom tanggal baru seperti Tahun, Bulan, dll. dengan menggunakan DAX.
Menambahkan kolom tanggal baru ke tabel tanggal
Tabel tanggal dengan kolom tanggal tunggal yang memiliki satu baris untuk setiap hari setiap tahun sangat penting untuk menetapkan semua tanggal dalam rentang tanggal. Juga diperlukan untuk membuat hubungan antara tabel fakta dan tabel tanggal. Tapi kolom tanggal tunggal dengan satu baris untuk setiap hari tidak berguna saat menganalisis menurut tanggal dalam laporan PivotTable atau Power View. Anda ingin tabel tanggal menyertakan kolom yang membantu mengagregasi data Anda untuk rentang atau grup tanggal. Misalnya, Anda mungkin ingin menjumlahkan jumlah penjualan menurut bulan atau kuartal, atau membuat pengukuran yang menghitung pertumbuhan tahun demi tahun. Dalam setiap kasus ini, tabel tanggal Anda memerlukan kolom tahun, bulan, atau kuartal yang memungkinkan Anda mengagregasi data Anda untuk periode tersebut.
Jika Anda mengimpor tabel tanggal dari sumber data relasional, tabel mungkin sudah menyertakan tipe kolom tanggal berbeda yang Anda inginkan. Dalam beberapa kasus, Anda mungkin ingin mengubah beberapa kolom tersebut atau membuat kolom tanggal tambahan. Hal ini berlaku terutama jika Anda membuat tabel tanggal Anda sendiri Excel dan menyalinnya ke dalam Model Data. Untungnya, membuat kolom tanggal baru di Power Pivot cukup mudah dengan Fungsi Tanggal dan Waktu dalam DAX.
Tips:Â Jika Anda belum bekerja dengan DAX, tempat yang bagus untuk mulai mempelajari adalah dengan Mulai Cepat: Pelajari Dasar-dasar DAX dalam 30 Menit Office.com.
Fungsi Tanggal dan Waktu DAX
Jika anda pernah bekerja dengan fungsi tanggal dan waktu dalam Excel, maka Anda mungkin akan terbiasa dengan Fungsi Tanggal dan Waktu. Meskipun fungsi ini mirip dengan fungsi-fungsi tersebut di Excel, ada beberapa perbedaan penting:
-
Fungsi Date dan Time DAX menggunakan tipe data datetime.
-
Bagan tersebut dapat mengambil nilai dari kolom sebagai argumen.
-
Kode ini dapat digunakan untuk mengembalikan dan/atau memanipulasi nilai tanggal.
Fungsi ini sering digunakan saat membuat kolom tanggal kustom dalam tabel tanggal, sehingga penting untuk dipahami. Kami akan menggunakan jumlah fungsi ini untuk membuat kolom untuk Tahun, Kuartal, Tahun Fiskal, dan lain-lain.
Catatan:Â Fungsi Date dan Time dalam DAX tidak sama dengan fungsi Time Intelligence. Pelajari selengkapnya tentang Kecerdasan Waktu di Power Pivot Excel 2013.
DAX mencakup fungsi Tanggal dan Waktu berikut:
Ada banyak fungsi DAX lain yang juga bisa Anda gunakan dalam rumus. Misalnya, banyak rumus yang diuraikan di sini menggunakan Fungsi Matematika dan Trigonometrik seperti MOD dan TRUNC,Fungsi Logika seperti IF, dan Fungsi Teks seperti FORMAT Untuk informasi selengkapnya tentang fungsi DAX lain, lihat bagian Sumber Daya Tambahan selanjutnya di artikel ini.
Contoh rumus untuk tahun kalender
Contoh berikut ini menjelaskan rumus yang digunakan untuk membuat kolom tambahan dalam tabel tanggal bernama Kalender. Satu kolom, bernama Tanggal, sudah ada dan berisi rentang tanggal yang bersebelahan dari 1/1/2010 hingga 31/12/2016.
Tahun
=YEAR([date])
Dalam rumus ini, fungsi YEAR mengembalikan tahun dari nilai dalam kolom Tanggal. Karena nilai dalam kolom Tanggal adalah tipe data datetime, fungsi YEAR mengetahui cara untuk mengembalikan tahun darinya.
Bulan
=MONTH([date])
Dalam rumus ini, seperti fungsi YEAR, kita dapat menggunakan fungsi MONTH untuk mengembalikan nilai bulan dari kolom Date.
Kuartal
=INT(([Month]+2)/3)
Dalam rumus ini, kami menggunakan fungsi INT untuk mengembalikan nilai tanggal sebagai bilangan bulat. Argumen yang kami tentukan untuk fungsi INT adalah nilai dari kolom Month, tambahkan 2 lalu bagi dengan 3 untuk mendapatkan kuartal kami, 1 hingga 4.
Nama Bulan
=FORMAT([date],"mmmm")
Dalam rumus ini, untuk mendapatkan nama bulan, kami menggunakan fungsi FORMAT untuk mengonversi nilai numerik dari kolom Tanggal menjadi teks. Kami menentukan kolom Tanggal sebagai argumen pertama, lalu format; kami ingin nama bulan kami memperlihatkan semua karakter, jadi kami menggunakan "mmmm". Hasil kami terlihat seperti ini:
Jika kita ingin mengembalikan nama bulan yang disingkat menjadi tiga huruf, kita akan menggunakan "mmm" dalam argumen format tersebut.
Hari dalam Minggu
=FORMAT([date],"ddd")
Dalam rumus ini, kami menggunakan fungsi FORMAT untuk mendapatkan nama hari. Karena kami hanya menginginkan nama hari yang disingkat, kami menentukan "ddd" dalam argumen format.
Contoh PivotTable
Setelah anda memiliki bidang untuk tanggal seperti Tahun, Kuartal, Bulan, dsb., Anda bisa menggunakannya dalam PivotTable atau laporan. Misalnya, gambar berikut ini memperlihatkan bidang SalesAmount dari tabel Fakta penjualan dalam VALUES, dan Tahun dan Kuartal dari tabel dimensi Kalender di ROWS. SalesAmount diagregasi untuk konteks tahun dan kuartal.
Contoh rumus untuk tahun fiskal
Tahun Fiskal
=IF([Month]<= 6,[Year],[Year]+1)
Dalam contoh ini, tahun fiskal dimulai pada tanggal 1 Juli.
Tidak ada fungsi yang bisa mengekstrak tahun fiskal dari nilai tanggal karena tanggal mulai dan berakhir untuk tahun fiskal sering berbeda dari tahun-tahun kalender. Untuk mendapatkan tahun fiskal, kami terlebih dahulu menggunakan fungsi IF untuk menguji apakah nilai untuk Bulan kurang dari atau sama dengan 6. Di argumen kedua, jika nilai untuk Bulan lebih kecil dari atau sama dengan 6, nilai dari kolom Tahun akan dikembalikan. Jika tidak, kembalikan nilai dari Tahun dan tambahkan 1.
Cara lain untuk menentukan nilai bulan akhir tahun fiskal adalah dengan membuat pengukuran yang hanya menentukan bulan. Misalnya, FYE:=6. Anda lalu bisa mereferensikan nama pengukuran di tempat angka bulan. Misalnya, =IF([Month]<=[FYE],[Year],[Year]+1). Ini menyediakan lebih banyak fleksibilitas saat merujuk bulan akhir tahun fiskal dalam beberapa rumus yangberbeda.
Bulan Fiskal
=IF([Month]<= 6, 6+[Month], [Month]- 6)
Dalam rumus ini, kami menentukan apakah nilai untuk [Bulan] kurang dari atau sama dengan 6, lalu ambil 6 dan tambahkan nilai dari Bulan, jika tidak kurangi 6 dari nilai dari [Bulan].
Kuartal Fiskal
=INT(([FiscalMonth]+2)/3)
Rumus yang kami gunakan untuk FiscalQuarter sama seperti untuk Kuartal dalam tahun kalender kami. Perbedaannya hanyalah kami yang menentukan [FiscalMonth] dan bukan [Month].
Hari libur atau tanggal khusus
Anda mungkin ingin menyertakan kolom tanggal yang menunjukkan tanggal tertentu adalah hari libur atau beberapa tanggal khusus lainnya. Misalnya, Anda mungkin ingin menjumlahkan total penjualan untuk hari Tahun Baru dengan menambahkan bidang Hari Libur ke PivotTable, sebagai pemotong, atau filter. Dalam kasus lain Anda mungkin ingin mengecualikan tanggal tersebut dari kolom tanggal lain atau dalam pengukuran.
Termasuk hari libur atau hari khusus yang sangat sederhana. Anda dapat membuat tabel dalam Excel berisi tanggal yang ingin disertakan. Anda kemudian dapat menyalin atau menggunakan Tambahkan ke Model Data untuk menambahkannya ke Model Data sebagai tabel tertaut. Dalam banyak kasus, tidak perlu membuat hubungan antara tabel dan tabel Kalender. Setiap rumus yang merujuk dapat menggunakan fungsi LOOKUPVALUE untuk mengembalikan nilai.
Di bawah ini adalah contoh tabel yang dibuat di Excel termasuk hari libur yang ditambahkan ke tabel tanggal:
|
Tanggal |
Hari Libur |
|---|---|
|
1/1/2010 |
Tahun Baru |
|
11/25/2010 |
Thanksgiving |
|
12/25/2010 |
Natal |
|
01/01/11 |
Tahun Baru |
|
11/24/2011 |
Thanksgiving |
|
12/25/2011 |
Natal |
|
01/01/2012 |
Tahun Baru |
|
22/11/2012 |
Thanksgiving |
|
12/25/2012 |
Natal |
|
1/1/2013 |
Tahun Baru |
|
11/28/2013 |
Thanksgiving |
|
12/25/2013 |
Natal |
|
11/27/2014 |
Thanksgiving |
|
12/25/2014 |
Natal |
|
01/01/2014 |
Tahun Baru |
|
11/27/2014 |
Thanksgiving |
|
12/25/2014 |
Natal |
|
1/1/2015 |
Tahun Baru |
|
11/26/2014 |
Thanksgiving |
|
12/25/2015 |
Natal |
|
01/01/2016 |
Tahun Baru |
|
11/24/2016 |
Thanksgiving |
|
12/25/2016 |
Natal |
Dalam tabel tanggal, kita membuat kolom bernama Hari Libur dan menggunakan rumus seperti ini:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Mari kita lihat rumus ini dengan lebih saksama.
Kami menggunakan fungsi LOOKUPVALUE untuk mendapatkan nilai dari kolom Hari Libur dalam tabel Hari Libur. Di argumen pertama, kami menentukan kolom di mana nilai hasil kita akan berada. Kami menentukan kolom Hari Libur dalam tabel Hari Libur karena itulah nilai yang ingin dikembalikan.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Kemudian kami menentukan argumen kedua, kolom pencarian yang memiliki tanggal yang ingin kami cari. Kami menentukan kolom Tanggal dalam tabel Hari Libur, seperti ini:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Terakhir, kita menentukan kolom dalam tabel Kalender kita yang mempunyai tanggal yang ingin kita cari di tabel Hari Libur. Tentu saja ini adalah kolom Tanggal dalam tabel Kalender.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Kolom Hari Libur akan mengembalikan nama hari libur untuk setiap baris yang memiliki nilai tanggal yang cocok dengan tanggal dalam tabel Hari Libur.
Kalender kustom - tiga belas periode empat minggu
Beberapa organisasi, seperti layanan ritel atau makanan, sering melaporkan periode yang berbeda, seperti tiga belas periode empat minggu. Dengan tiga belas kalender periode empat minggu, setiap periode adalah 28 hari; oleh karena itu, setiap periode berisi empat Senin, empat Selasa, empat Rabu, dan sebagainya. Setiap periode berisi jumlah hari yang sama, dan biasanya, hari libur akan berada dalam periode yang sama setiap tahun. Anda bisa memilih untuk memulai periode pada hari apa pun dalam seminggu. Sama seperti tanggal dalam kalender atau tahun fiskal, Anda bisa menggunakan DAX untuk membuat kolom tambahan dengan tanggal kustom.
Dalam contoh di bawah, periode penuh pertama dimulai pada Hari Minggu pertama tahun fiskal. Dalam hal ini, tahun fiskal dimulai pada 1/7.
Minggu
Nilai ini memberi kita nomor minggu yang dimulai dengan minggu penuh pertama dalam tahun fiskal. Dalam contoh ini, minggu penuh pertama dimulai pada hari Minggu, sehingga minggu penuh pertama dalam tahun fiskal pertama dalam tabel Kalender sebenarnya dimulai pada 04/07/2010 dan berlanjut hingga minggu penuh terakhir di tabel Kalender. Meskipun nilai ini tidak semuanya berguna dalam analisis, penting untuk menghitung penggunaan dalam rumus periode 28 hari lainnya.
=INT([date]-40356)/7)
Mari kita lihat rumus ini dengan lebih saksama.
Pertama, kami membuat rumus yang mengembalikan nilai dari kolom Tanggal sebagai bilangan bulat, seperti ini:
=INT([date])
Kita kemudian ingin mencari minggu pertama dalam tahun fiskal pertama. Kami melihat bahwa itu adalah 04/07/2010.
Sekarang, kurangi 40356 (yang merupakan bilangan bulat untuk 27/6/2010, Minggu terakhir dari tahun fiskal sebelumnya) dari nilai itu untuk mendapatkan jumlah hari sejak awal hari dalam tabel Kalender kami, seperti ini:
=INT([date]-40356)
Lalu membagi hasilnya dengan 7 (hari dalam seminggu), seperti ini:
=INT(([date]-40356)/7)
Hasilnya terlihat seperti ini:
Period (Titik)
Periode dalam kalender kustom ini berisi 28 hari dan akan selalu dimulai pada hari Minggu. Kolom ini akan mengembalikan jumlah periode yang dimulai dengan Minggu pertama dalam tahun fiskal pertama.
=INT(([Week]+3)/4)
Mari kita lihat rumus ini dengan lebih saksama.
Pertama, kami membuat rumus yang mengembalikan nilai dari kolom Week sebagai bilangan bulat, seperti ini:
=INT([Week])
Lalu tambahkan 3 ke nilai tersebut, seperti ini:
=INT([Week]+3)
Lalu bagi hasilnya dengan 4, seperti ini:
=INT(([Week]+3)/4)
Hasilnya terlihat seperti ini:
Periode Tahun Fiskal
Nilai ini mengembalikan tahun fiskal untuk suatu periode.
=INT(([Periode]+12)/13)+2008
Mari kita lihat rumus ini dengan lebih saksama.
Pertama, kami membuat rumus yang mengembalikan nilai dari Periode dan menambahkan 12:
= ([Titik]+12)
Kami membagi hasilnya dengan 13, karena ada tiga belas periode 28 hari dalam tahun fiskal:
=(([Titik]+12)/13)
Kami menambahkan 2010, karena itu adalah tahun pertama dalam tabel:
=(([Titik]+12)/13)+2010
Akhirnya kita menggunakan fungsi INT untuk menghapus pecahan hasil, dan mengembalikan bilangan keseluruhan, bila dibagi dengan 13, seperti ini:
=INT(([Periode]+12)/13)+2010
Hasilnya terlihat seperti ini:
Periode dalam FiscalYear
Nilai ini mengembalikan nomor periode, 1 – 13, dimulai dengan Periode penuh pertama (dimulai pada hari Minggu) dalam setiap tahun fiskal.
=IF(MOD([Periode],13), MOD([Periode],13),13)
Rumus ini sedikit lebih kompleks, jadi kami akan menjelaskannya terlebih dahulu dalam bahasa yang lebih kami pahami. Rumus ini menyatakan, bagi nilai dari [Periode] dengan 13 untuk mendapatkan angka periode (1-13) pada tahun. Jika angka tersebut adalah 0, kembalikan 13.
Pertama, kita membuat rumus yang mengembalikan sisa nilai dari Periode dengan 13. Kita bisa menggunakan MOD (fungsi Matematika dan Trigonometrik) seperti ini:
=MOD([Titik],13)
Ini, sebagian besar, memberi kita hasil yang kita inginkan, kecuali di mana nilai untuk Periode adalah 0 karena tanggal tersebut tidak berada dalam tahun fiskal pertama, seperti dalam lima hari pertama di contoh tabel tanggal Kalender kami. Kami dapat menangani hal ini dengan fungsi IF. Dalam kasus hasil kami adalah 0, kami mengembalikan 13, seperti ini:
=IF(MOD([Periode],13),MOD([Periode],13),13)
Hasilnya terlihat seperti ini:
Contoh PivotTable
Gambar di bawah ini memperlihatkan PivotTable dengan bidang JumlahJualan dari tabel Fakta penjualan dalam NILAI, dan Bidang PeriodFiscalYear dan PeriodInFiscalYear dari tabel dimensi tanggal Kalender di ROWS. SalesAmount diagregasi untuk konteks menurut tahun fiskal dan periode 28 hari dalam tahun fiskal.
Hubungan
Setelah membuat tabel tanggal dalam Model Data, untuk mulai menelusuri data dalam PivotTable dan laporan, serta untuk menggabungkan data berdasarkan kolom dalam tabel dimensi tanggal, Anda perlu membuat hubungan antara tabel fakta dengan data transaksi dan tabel tanggal.
Karena Anda perlu membuat hubungan berdasarkan tanggal, Anda mungkin ingin memastikan Anda membuat hubungan itu antara kolom yang nilainya adalah tipe data datetime (Date).
Untuk setiap nilai tanggal dalam tabel fakta, kolom pencarian terkait dalam tabel tanggal harus berisi nilai yang cocok. Misalnya, baris (catatan transaksi) di dalam tabel Fakta penjualan dengan nilai 15/8/2012 12:00 DI kolom DateKey harus memiliki nilai terkait dalam kolom Tanggal terkait di tabel tanggal (bernama Kalender). Hal ini merupakan salah satu alasan terpenting mengapa Anda ingin kolom tanggal di tabel tanggal berisi rentang tanggal yang berdekatan dengan kemungkinan tanggal dalam tabel fakta.
Catatan:Â Meskipun kolom tanggal dalam setiap tabel harus memiliki tipe data yang sama (Tanggal), format setiap kolom tidak masalah..
Catatan:Â Jika Power Pivot tidak memungkinkan Anda membuat hubungan di antara dua tabel, bidang tanggal mungkin tidak menyimpan tanggal dan waktu ke tingkat presisi yang sama. Tergantung pada pemformatan kolom, nilai mungkin terlihat sama, namun disimpan secara berbeda. Baca selengkapnya tentang bekerja dengan waktu.
Catatan:Â Hindari menggunakan tombol pengganti bilangan bulat dalam hubungan. Ketika Anda mengimpor data dari sumber data relasional, sering kali kolom tanggal dan waktu dinyatakan dengan tombol pengganti, yang merupakan kolom bilangan bulat yang digunakan untuk menyatakan tanggal yang unik. Di Power Pivot, Anda harus menghindari pembuatan hubungan dengan menggunakan tombol tanggal/waktu bilangan bulat, dan sebagai gantinya, gunakan kolom yang berisi nilai unik dengan tipe data tanggal. Meskipun penggunaan tombol pengganti dianggap sebagai cara terbaik dalam gudang data tradisional, tombol bilangan bulat tidak diperlukan di Power Pivot dan dapat menyulitkan untuk mengelompokkan nilai dalam PivotTable menurut periode tanggal yang berbeda.
Jika Anda mendapatkan kesalahan Tipe yang tidak cocok ketika mencoba membuat hubungan, kemungkinan karena kolom di tabel fakta bukan tipe data Tanggal. Ini bisa terjadi saat Power Pivot tidak bisa mengonversi non-tanggal (biasanya tipe data teks) menjadi tipe data tanggal secara otomatis. Anda masih bisa menggunakan kolom di tabel fakta, tapi Anda harus mengonversi data dengan rumus DAX dalam kolom terhitung baru. Lihat Mengonversi tanggal tipe data teks menjadi tipe data tanggal nanti di lampiran.
Beberapa hubungan
Dalam beberapa kasus, mungkin perlu membuat beberapa hubungan atau membuat beberapa tabel tanggal. Misalnya, jika ada beberapa bidang tanggal dalam tabel Fakta penjualan, seperti DateKey, ShipDate, dan ReturnDate, semuanya bisa memiliki hubungan dengan bidang Tanggal dalam tabel tanggal Kalender, tapi hanya salah satu yang bisa menjadi hubungan aktif. Dalam kasus ini, karena DateKey mewakili tanggal transaksi, oleh karena itu tanggal yang paling penting, ini akan berfungsi sebagai hubungan aktif. Yang lain mempunyai hubungan yang tidak aktif.
PivotTable berikut menghitung total penjualan dengan Tahun Fiskal dan Kuartal Fiskal. Pengukuran bernama Total Sales, dengan rumus Total Sales:=SUM([SalesAmount]), ditempatkan dalam NILAI, serta bidang FiscalYear dan FiscalQuarter dari tabel tanggal Kalender ditempatkan di ROWS.
PivotTable langsung dapat berfungsi dengan benar karena kami ingin menjumlahkan total penjualan berdasarkan tanggal transaksidalam DateKey. Pengukuran Total Penjualan kami menggunakan tanggal dalam DateKey dan dijumlahkan dengan tahun fiskal dan kuartal fiskal karena ada hubungan antara DateKey dalam tabel Penjualan dan kolom Tanggal di tabel tanggal Kalender.
Hubungan tidak aktif
Namun, bagaimana jika kami ingin menjumlahkan total penjualan bukan menurut tanggal transaksi, tetapi menurut tanggal pengiriman? Kita memerlukan hubungan antara kolom ShipDate dalam tabel Penjualan dan kolom Tanggal dalam tabel Kalender. Jika kami tidak membuat hubungan itu, agregasi kami selalu berdasarkan tanggal transaksi. Namun, kita dapat memiliki beberapa hubungan, meskipun hanya satu yang dapat aktif, dan karena tanggal transaksi merupakan tanggal yang paling penting, tanggal tersebut akan mendapatkan hubungan aktif dengan tabel Kalender.
Dalam hal ini, ShipDate memiliki hubungan yang tidak aktif, sehingga setiap rumus pengukuran yang dibuat untuk menggabungkan data berdasarkan tanggal pengiriman harus menentukan hubungan yang tidak aktif dengan menggunakan fungsi USERELATIONSHIP.
Misalnya, karena terdapat hubungan tidak aktif antara kolom Tanggal Pengiriman dalam tabel Penjualan dan kolom Tanggal dalam tabel Kalender, kita dapat membuat pengukuran yang menjumlahkan total penjualan menurut tanggal pengiriman. Kita menggunakan rumus seperti ini untuk menentukan hubungan yang akan digunakan:
Total Penjualan menurut Tanggal Pengiriman:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Rumus ini hanya menyatakan: Hitung jumlah untuk JumlahJualan, tetapi filter menggunakan hubungan antara kolom Tanggal Pengiriman dalam tabel Penjualan dan kolom Tanggal dalam tabel Kalender.
Sekarang, jika kita membuat PivotTable dan meletakkan Total Penjualan dengan pengukuran Tanggal Pengiriman dalam NILAI, dan Tahun Fiskal dan Kuartal Fiskal pada ROWS, kita melihat Total Keseluruhan yang sama, tetapi semua jumlah jumlah lainnya untuk tahun fiskal dan kuartal fiskal berbeda karena berdasarkan tanggal pengiriman dan bukan tanggal transaksi.
Menggunakan hubungan tidak aktif memungkinkan Anda menggunakan hanya satu tabel tanggal, tapi memang mengharuskan setiap pengukuran (seperti Total Sales by Ship Date), referensikan hubungan yang tidak aktif dalam rumusnya. Alternatif lainnya, adalah, gunakan beberapa tabel tanggal.
Beberapa tabel tanggal
Cara lain untuk bekerja dengan beberapa kolom tanggal di tabel fakta Anda adalah dengan membuat beberapa tabel tanggal, dan membuat hubungan aktif yang terpisah di antaranya. Mari kita lihat contoh tabel Penjualan kita lagi. Kami memiliki tiga kolom dengan tanggal yang mungkin ingin kami agregasi data di:
-
DateKey dengan tanggal penjualan untuk setiap transaksi.
-
Tanggal Pengiriman – dengan tanggal dan waktu saat item terjual telah dikirim ke pelanggan.
-
ReturnDate – dengan tanggal dan waktu saat satu atau beberapa item yang dikembalikan diterima.
Ingat, bidang DateKey dengan tanggal transaksi paling penting. Kami akan melakukan sebagian besar agregasi kami berdasarkan tanggal-tanggal tersebut, jadi tentu kami pasti menginginkan hubungan antara agregasi itu dengan kolom Tanggal di tabel Kalender. Jika tidak ingin membuat hubungan yang tidak aktif antara Tanggal Pengiriman dan TanggalMutangan dan bidang Tanggal dalam tabel Kalender, sehingga memerlukan rumus pengukuran khusus, kita dapat membuat tabel tanggal tambahan untuk tanggal pengiriman dan tanggal pengembalian. Kemudian, kami dapat membuat hubungan aktif di antara keduanya.
Dalam contoh ini, kami telah membuat tabel tanggal lain bernama ShipCalendar. Ini, tentu saja, juga berarti membuat kolom tanggal tambahan, dan karena kolom tanggal ini berada dalam tabel tanggal berbeda, kami ingin memberi nama dengan cara yang membedakannya dari kolom yang sama di tabel Kalender. Misalnya, kami telah membuat kolom bernama ShipYear, ShipMonth, ShipQuarter, dan lain-lain.
Jika kami membuat PivotTable dan meletakkan pengukuran Total Sales dalam VALUES, dan ShipFiscalYear serta ShipFiscalQuarter di ROWS, kami akan melihat hasil yang sama seperti yang dilihat saat membuat hubungan tidak aktif dan bidang terhitung Total Sales by Ship Date khusus.
Setiap pendekatan ini memerlukan pertimbangan yang hati-hati. Ketika menggunakan beberapa hubungan dengan tabel tanggal tunggal, Anda mungkin harus membuat pengukuran khusus yang transit hubungan tidak aktif dengan menggunakan fungsi USERELATIONSHIP. Di sisi lain, membuat beberapa tabel tanggal dapat membingungkan dalam Daftar Bidang, dan karena Anda memiliki lebih banyak tabel dalam Model Data, pembuatannya akan memerlukan lebih banyak memori. Lakukan eksperimen dengan hal yang paling baik untuk Anda.
Properti Date Table
Properti Date Table mengatur metadata yang diperlukan Time-Intelligence fungsi seperti TOTALYTD, PREVIOUSMONTH, dan DATESBETWEEN agar berfungsi dengan benar. Saat perhitungan dijalankan menggunakan salah satu fungsi tersebut, mesin rumus Power Pivot mengetahui ke mana harus mendapatkan tanggal yang diperlukannya.
Peringatan:Â Jika properti ini tidak diatur, pengukuran menggunakan fungsi DAX Time-Intelligence mungkin tidak mengembalikan hasil yang benar.
Ketika mengatur properti Tabel Tanggal, tentukan tabel tanggal dan kolom tanggal tipe data Tanggal (datetime) di dalamnya.
Caranya: Mengatur properti Tabel Tanggal
-
Di jendela PowerPivot, pilih tabel Kalender.
-
Pada tab Desain, klik Tandai sebagai tabel tanggal.
-
Dalam kotak dialog Tandai sebagai Tabel Tanggal, pilih kolom dengan nilai unik dan tipe data Tanggal.
Bekerja dengan waktu
Semua nilai tanggal dengan tipe data Tanggal dalam Excel atau SQL Server sebenarnya adalah angka. Angka yang disertakan dalam angka tersebut adalah digit yang merujuk ke waktu. Dalam banyak kasus, waktu itu untuk masing-masing dan setiap baris tengah malam. Misalnya, jika bidang DateTimeKey dalam tabel Fakta penjualan memiliki nilai seperti 19/10/2010 12:00:00, ini berarti bahwa nilainya adalah untuk tingkat presisi hari tersebut. Jika nilai bidang DateTimeKey memiliki waktu yang disertakan, misalnya, 19/10/2010 8:44:00 AM, ini berarti bahwa nilai itu berada di tingkat presisi menit. Nilai juga bisa mencapai presisi tingkat jam, atau bahkan tingkat presisi detik. Tingkat presisi dalam nilai waktu akan berdampak signifikan pada cara Anda membuat tabel tanggal Anda dan hubungan antara tabel itu dan fakta Anda.
Anda perlu menentukan apakah Anda akan mengagregasi data Anda ke tingkat presisi sehari atau ke tingkat presisi waktu. Dengan kata lain, Anda mungkin ingin menggunakan kolom dalam tabel tanggal Anda seperti area Pagi, Sore, atau Jam sebagai tanggal waktu dalam area Baris, Kolom, atau Filter PivotTable.
Catatan:Â Hari adalah satuan waktu terkecil yang dapat digunakan oleh fungsi Inteligensi Waktu DAX. Jika Anda tidak perlu bekerja dengan nilai waktu, Anda harus mengurangi presisi data Anda untuk menggunakan hari sebagai unit minimum.
Jika ingin mengagregasi data Anda ke tingkat waktu, tabel tanggal memerlukan kolom tanggal dengan waktu yang disertakan. Bahkan, itu akan membutuhkan kolom tanggal dengan satu baris untuk setiap jam, atau mungkin bahkan setiap menit, dari setiap hari, untuk setiap tahun dalam rentang tanggal. Ini karena, untuk membuat hubungan antara kolom DateTimeKey di tabel fakta dan kolom tanggal dalam tabel tanggal, Anda harus memiliki nilai yang cocok. Seperti yang bisa Dibayangkan, jika Anda telah memasukkan banyak tahun, ini bisa dibuat untuk tabel tanggal yang sangat besar.
Namun, pada kebanyakan kasus, Anda ingin menggabungkan data hanya ke satu hari. Dengan kata lain, Anda akan menggunakan kolom seperti Tahun, Bulan, Minggu, atau Hari dalam Minggu sebagai bidang di area Baris, Kolom, atau Filter PivotTable. Dalam hal ini, kolom tanggal dalam tabel tanggal hanya perlu berisi satu baris untuk setiap hari dalam satu tahun, seperti yang telah kami uraikan sebelumnya.
Jika kolom tanggal Anda menyertakan tingkat waktu presisi, tapi Anda akan mengagregasi hanya ke tingkat hari, untuk membuat hubungan antara tabel fakta dan tabel tanggal, Anda mungkin harus memodifikasi tabel fakta Anda dengan membuat kolom baru yang memotong nilai di kolom tanggal menjadi nilai hari. Dengan kata lain, konversi nilai seperti 19/10/2010 8:44:00AM hingga 10/19/2010 12:00:00 AM. Selanjutnya Anda bisa membuat hubungan antara kolom baru ini dan kolom tanggal dalam tabel tanggal karena nilainya cocok.
Mari kita lihat contohnya. Gambar ini memperlihatkan kolom DateTimeKey dalam tabel Fakta penjualan. Semua agregasi untuk data dalam tabel ini hanya perlu untuk tingkat hari, dengan menggunakan kolom dalam tabel tanggal Kalender seperti Tahun, Bulan, Kuartal, dll. Waktu yang disertakan dalam nilai tidak relevan, hanya tanggal aktual.
Karena kita tidak perlu menganalisis data ini hingga mencapai tingkat waktu, kita tidak memerlukan kolom Tanggal dalam tabel tanggal Kalender untuk menyertakan satu baris setiap jam dan setiap menit setiap hari dalam setiap tahun. Jadi, kolom Tanggal dalam tabel tanggal kami terlihat seperti ini:
Untuk membuat hubungan antara kolom DateTimeKey dalam tabel Penjualan dan kolom Tanggal dalam tabel Kalender, kita dapat membuat kolom terhitung baru di tabel Sales fact, dan menggunakan fungsi TRUNC untuk memotong nilai tanggal dan waktu di kolom DateTimeKey menjadi nilai tanggal yang cocok dengan nilai di kolom Tanggal dalam tabel Kalender. Rumus kami terlihat seperti ini:
=TRUNC([DateTimeKey],0)
Ini memberi kita kolom baru (kami bernama DateKey) dengan tanggal dari kolom DateTimeKey, dan waktu 12:00:00 di setiap baris:
Sekarang kita bisa membuat hubungan antara kolom baru ini (DateKey) dan kolom Tanggal di tabel Kalender.
Demikian pula, kita bisa membuat kolom terhitung dalam tabel Penjualan yang mengurangi presisi waktu dalam kolom DateTimeKey ke tingkat presisi jam. Dalam hal ini, fungsi TRUNC tidak akan berfungsi, tetapi kami masih dapat menggunakan fungsi DAX Date dan Time lainnya untuk mengekstrak dan menggabungkan kembali nilai baru untuk tingkat presisi suatu jam. Kita bisa menggunakan rumus seperti ini:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Kolom baru kami terlihat seperti ini:
Jika kolom Tanggal dalam tabel tanggal memiliki nilai untuk tingkat presisi jam, kita dapat membuat hubungan di antaranya.
Membuat tanggal lebih dapat digunakan
Banyak kolom tanggal yang Anda buat dalam tabel tanggal diperlukan untuk bidang lain, tapi sebenarnya tidak semua yang berguna dalam analisis. Misalnya, bidang DateKey dalam tabel Penjualan yang telah kami rujuk dan ditampilkan di seluruh artikel ini penting karena untuk setiap transaksi, transaksi tersebut direkam sebagai terjadi pada tanggal dan waktu tertentu. Tapi dari analisis dan titik pelaporan tampilan, tidak semua berguna karena kita tidak bisa menggunakannya sebagai bidang baris, kolom, atau filter dalam Tabel Pivot atau laporan.
Demikian pula, dalam contoh kami, kolom Tanggal dalam tabel Kalender sangat berguna dan sangat penting, tetapi Anda tidak dapat menggunakannya sebagai dimensi dalam PivotTable.
Untuk mempertahankan tabel dan kolom di dalamnya sebaik mungkin, dan untuk membuat daftar Bidang laporan PivotTable atau Power View lebih mudah dinavigasikan, penting untuk menyembunyikan kolom yang tidak diperlukan dari alat klien. Anda mungkin juga ingin menyembunyikan tabel tertentu. Tabel Hari Libur yang diperlihatkan sebelumnya berisi tanggal hari libur yang penting bagi kolom tertentu dalam tabel Kalender, tetapi Anda tidak dapat menggunakan kolom Tanggal dan Hari Libur dalam tabel Hari Libur sendiri sebagai bidang dalam PivotTable. Here again, to make Field Lists easier to navigate, you can hide the whole Holidays table.
Aspek penting lain yang berfungsi dengan tanggal adalah konvensi penamaan. Anda bisa memberi nama tabel dan kolom di Power Pivot apa pun yang Anda inginkan. Tapi ingatlah, khususnya jika Anda akan berbagi buku kerja dengan pengguna lain, konvensi penamaan yang bagus memudahkan untuk mengidentifikasi tabel dan tanggal, tidak hanya di Daftar Bidang, tapi juga di Power Pivot dan dalam rumus DAX.
Setelah Anda memiliki tabel tanggal dalam Model Data, Anda bisa mulai membuat pengukuran yang akan membantu Anda mendapatkan yang terbaik dari data Anda. Beberapa di antaranya mungkin sesederhana menjumlahkan total penjualan untuk tahun ini, dan lainnya mungkin lebih kompleks, di mana Anda perlu memfilter rentang tanggal tertentu yang unik. Pelajari selengkapnya dalam Pengukuran di Power Pivot dan Fungsi Kecerdasan Waktu.
Lampiran
Mengonversi tanggal tipe data teks menjadi tipe data tanggal
Dalam beberapa kasus, tabel fakta dengan data transaksi mungkin berisi tanggal tipe data teks. Artinya, tanggal yang muncul sebagai 2012-12-04T11:47:09 sebenarnya bukan tanggal sama sekali, atau setidaknya bukan tipe tanggal Power Pivot yang bisa dipahami. Benar-benar hanya teks yang terbaca seperti tanggal. Untuk membuat hubungan antara kolom tanggal dalam tabel fakta dan kolom tanggal dalam tabel tanggal, kedua kolom harus merupakan tipe data Tanggal.
Biasanya, ketika Anda mencoba mengubah tipe data untuk kolom tanggal yang merupakan tipe data teks menjadi tipe data tanggal, Power Pivot bisa menginterpretasikan tanggal dan mengonversinya menjadi tipe data tanggal yang benar secara otomatis. Jika Power Pivot tidak bisa melakukan konversi tipe data, Anda akan mendapatkan kesalahan tipe ketidakcocokan.
Namun, Anda masih bisa mengonversi tanggal menjadi tipe data tanggal yang benar. Anda bisa membuat kolom terhitung baru dan menggunakan rumus DAX untuk menguraikan tahun, bulan, hari, waktu, dsb. dari string teks lalu menggabungkannya kembali bersama-sama dengan cara Power Pivot bisa membaca sebagai tanggal asli.
Dalam contoh ini, kami telah mengimpor tabel fakta bernama Sales ke dalam Power Pivot. Tabel ini berisi kolom bernama DateTime. Nilai muncul seperti ini:
Jika kita melihat Tipe Data dalam tab Beranda grup Pemformatan Power Pivot, kita melihat bahwa itu adalah tipe data Teks.
Kami tidak dapat membuat hubungan antara kolom DateTime dan kolom Tanggal dalam tabel tanggal karena tipe data tidak cocok. Jika mencoba mengubah tipe data menjadi Tanggal,kami mendapatkan kesalahan ketidakcocokan tipe:
Dalam kasus ini, Power Pivot tidak bisa mengonversi tipe data dari teks ke tanggal. Kita masih bisa menggunakan kolom ini, tapi untuk mendapatkannya ke dalam tipe data tanggal sebenarnya, kita harus membuat kolom baru yang memilah teks dan membuatnya kembali menjadi nilai Power Pivot bisa membuat tipe data Tanggal.
Ingat, dari bagian Bekerja dengan waktu sebelumnya dalam artikel ini; kecuali analisis Anda harus ke tingkat presisi waktu-hari, Anda harus mengonversi tanggal di tabel fakta Anda ke tingkat presisi hari. Dengan ingatlah bahwa, kami ingin nilai di kolom baru kami berada di tingkat presisi hari (tidak termasuk waktu). Kita bisa mengonversi nilai dalam kolom DateTime ke tipe data tanggal dan menghapus tingkat presisi waktu dengan rumus berikut:
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
Ini memberi kita kolom baru (dalam kasus ini, bernama Tanggal). Power Pivot bahkan mendeteksi nilai sebagai tanggal dan mengatur tipe data secara otomatis ke Tanggal.
Jika ingin mempertahankan tingkat presisi waktu, kami hanya perlu memperluas rumus untuk menyertakan jam, menit, dan detik.
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +
TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))
Sekarang setelah kita memiliki kolom Tanggal dari tipe data Tanggal, kita bisa membuat hubungan antara tipe data tanggal tersebut dengan kolom tanggal dalam tanggal.
Sumber daya tambahan
Mulai Cepat: Mempelajari Dasar-Dasar DAX Dalam 30 Menit
