Modifikasi End Of Month di Power BI

Pada grup PBI ada sebuah pertanyaan dari member dengan nama Gregory Gue. Dia menanyakan bagaimana membuat rumusan DAX untuk total penjualan per bulan. Jika perusahaannya menggunakan waktu yang berbeda. 

Perusahaan si Gregory ini agak nyeleneh dengan menentukan end of month adalah hari sabtu terakhir tiap bulannya.

Menentukan end of month dalam kondisi normal tentunya sangat mudah pada Power BI. Banyak fungsi yang dapat digunakan, seperti ENDOFMONTH[date] atau MAX[date]. Beda kasusnya jika end of month adalah hari sabtu terakhir... jan ngawur tenan rek

Begini kepusingan si Gregory:

I want to do a measure like

Custom Month to date =

CALCULATE (

    [Total Sales Amount],

    Filter(table name [Invoice_date_stn_inv_dt] >= table [Start of the month]) and table[End of month] )

Saya dan kolega saya pada diskusi grup tersebut, menyarankan solusi dengan menggunakan tabel Kelender/Tanggalan. Logikanya invoice date akan di lookup pada tabel Kalender sehingga akan lebih mudah melakukan pengaturan rentang waktu menggunakan tabel Kalender.

Pada tabel kalender dapat dengan mudah melakukan pembobotan atau indeks dari setiap tanggal atau hari. Perjanjian international telah menentukan urutan nomor hari adalah 1 = Minggu sampai 7 = Sabtu.

Solusinya

Solusinya adalah dengan membuat bobot/indeks/flag atau nilai (1) pada hari sabtu terakhir. sehingga akan dengan mudah menentukan rentang waktu antara awal bulan dengan tanggal yg memiliki nilai (1) pada bulan tersebut.

Pertama, buat kolom nomor hari di tabel kalender (1-7), weekday[Date]

Kedua, buat kolom yang melakukan pembobotan 0 jika tanggal tersebut bukan lah hari sabtu (!=7) dan berikan nilai 1 jika tanggal tersebut adalah hari sabtu terakhir di bulan tersebut

Ekspresi DAX nya adalah:

Hari Sabtu Terakhir = IF(DATEADD(Tanggalan[Date],7,DAY) > ENDOFMONTH(Tanggalan[Date])  &&  Tanggalan[Hari]= 7,1,0)

DATEADD(Tanggalan[Date],7,DAY) = tambahkan 7 hari kedepan pada sebuah tanggal

ENDOFMONTH(Tanggalan[Date]) = menentukan hari terakhir dari bulan terkait

Tanggalan[Hari] = tentukan hari tersebut adalah sabtu (7)

Hasilnya dapat dilihat pada gambar diatas, Tgl 25 Januari 2020 adalah hari sabtu terakhir pada bulan

tersebut.

 

Diskusi lengkap bisa anda lihat pada link berikut ini:

pbiusergroup/modifikasiEOM

#powerbi
#dax

EXCEL XLOOKUP brooo...!!!

EXCEL XLOOKUP brooo...!!!

Hari ini ada sebuah tawaran unik pada situs upwork.com. Tawarannya adalah sebagai berikut:

URGENT! We need this completed within the hour.

We need help with a quick vlookup formula.

If column B in sheet 1 matches column A in sheet 2, then pull data from column B in sheet 2 and place it into Column P in sheet 1

EZEEET kata anak jaman now

Microsoft Excel memiliki beberapa fungsi lookup yaitu, lookup, vlookup (yang sangat populer) dan hlookup. Silahkan anda cari definisi dari masing-masing fungsi tersebut.

Untuk kebutuhan tugas diatas, terdapat 1 fitur terbaru dari excel yaitu XLOOKUP. XLOOKUP adalah versi fleksibel dari keluarga LOOKUP. Fitur ini dapat melakukan pencocokan dalam rentang data secara kolom maupun baris. XLOOKUP juga dilengkapi dengan kemampuan pencocokan approximate ,exact matching dan wildcards (* ?) untuk pencocokan parsial.

Rumus dasarnya adalah = XLOOKUP(Referensi kolom/nilai; kolom/nilai yang dicocokan; kolom/nilai yang akan ditampilkan)

Mari kita bedah soal diatas

Gambar diatas menampilkan dua buah lembar kerja Excel. Nomor pelanggan pada kolom B menjadi kolom referensi. Cust No, pada kolom A di lembar lainnya menjadi data yang akan dicocokan. sedangkan kolom B pada lembar kerja tersebut menjadi nilai yang akan dikembalikan.

Maka persamaan yang dapat dibuat adalah XLOOKUP(B3;Turnover!$A$2:$A$236;Turnover!$B$2:$B$236;0)

B3 adalah nomor pelanggan
Turnover!$A$2:$A$236 adalah rentang data
Turnover!$B$2:$B$236 adalah nilai yang dikembalikan
0 adalah nilai yang dikembalikan jika tidak ada kecocokan Lookup

XLOOKUP menurut saya adalah fungsi yang sangat memudahkan  dan fleksibel. Tidak hanya LOOKUP sebuah nilai namun penggabungan teks juga dapat dilakukan oleh fitur ini.

Terima Kasih

#excel
#simpeldata
#simpledata
#ETL
#datapreparation

Ekstrak Data dan Normalisasi Data

 Ekstrak Data dan Normalisasi Data

Tulisan kali ini menyampaikan hasil pekerjaan dengan orang asing. Ini adalah pekerjaan pertama yang saya dapatkan dari situs freelancer www.upwork.com. Silahkan teman-teman mencoba disana.

Ekstrak data dan normalisasi adalah istilah-istilah yang sering saya dengan dan sering saya "lakukan". YA, ada quot pada kata lakukan. Artinya saya merasa telah melakukan ekstrak data dan normalisasi. tapi apakah ekstrak dan normalisasi itu sudah benar. Disini lah ujian pertama yang harus saya hadapi secara remote antara +62 dan +1. 

Sejak memasuki dunia data tahun 2015, ekstrak data dan normalisasi adalah hal yang biasa. Data-data yang diperoleh dari API, Web Service maupun CSV/Excel dibaca dengan mudah menggunakan Pentaho, MS Studio dan Talend Open Studio. YA, mereka terdiri dari ratusan ribu baris, tapi mereka sudah terformat dalam struktur. Apakah itu XML, CSV, JSON dan berisikan 1 buah nilai data untuk setiap cell-nya.Sehingga saya yakin dapat melakukannya. 

Bagaimana dengan data dalam bentuk kalimat tanpa struktur???.. Alhasil butuh 1 minggu agar saya dapat paham bagaimana cara ekstrak data. Berikut contoh datanya.

Yang harus anda lakukan adalah memilah dan mengambil kata, huruf dan angka yang memiliki kontribusi terhadap perhitungan. 

Tempatkan setiap temuan anda dalam usulan rencana kolom/field, dan kumpulkan setiap nilai yang ditemukan dalam usulan nilai. Selanjutnya lakukan normalisasi dari setiap nilai/value dan tentukan bentuk widget yang sesuai (single select, multi select)

Contoh data:

Any disputes or missing time must be made to Qualivis within 21 days from the end of the work week (15

days from invoice date). FILL OUT QUALIVIS TIME CORRECTION FORM AND SUBMIT IT TO BILLINGQUESTIONS@QUALIVIS.COM

Hasil Ekstrak:

1. Qualivis
2. 21 
3. 15
4. billingquestions@qualivis.com
5. day(s)

Hasil Normalisasi

1. Angka/Int = 21,15
2. String = Qualivis
3. Email = billingquestions@gualivis.com
4. Variabel = days

Dari pekerjaan diatas akan disusun kedalam dokumen pengembangan sebagai acuan para programmer dan DBA

Title	Data Type	Field Name	Values	Tooltip
Call Off Option	String	Call Off Option	None, Guaranteed, Not Guaranteed, Varied, Facility
Call Off Value	Integer	Call Off Value	1,2,3,12, 24…	Represent number of hour, week, shift
Call Off Units	String	Call Off Var	Shift, hours, weeks	This value represent the unit of value
Call Off Statement	String	Call Off Statement	l only if unit is closed, l no call off but they reserve the right to float them to get hours l Holiday facility call off	Additional information regarding to call off

Apa yang telah saya kerjaan tidak 100% memuaskan klien di US, tapi mereka dapat menerimanya. 

Ini pengalaman yang sangat berharga. Saat ini saya bisa mengatakan bahwa mampu dalam ekstrak dan normalisasi data dan bukan jagoan kandang. 

#excel

#ETL

#data analyst

#data extraction

#data normalization