Forum Discussion
Employee data modelling
Hi,
You should place all Excel files (one for each month and with the same structure month on month) in a single folder. Using Query Editor of PowerBI desktop, append data from all Excel files. Create a Calendar Table and build a relatioship from the Date column of your appended dataset to the Date column of the Calendar Table.
We can then write measures to model your data and build visuals.
Have a look at the TOTALMTD and PARALLELPERIOD functions:
https://msdn.microsoft.com/en-us/library/ee634560.aspx
Thanks Greg for the link, but i would need some help to model the data. Should i place the data in separate excel files for each months, or should i upload only the changes each month?.. Note that every month i will have same set of employees with 1 or 2 new joiners or leavers etc.. Would you be able to assist with this?
Thank you
In general, tabular models like denormalized data so I would put it all in a single table with maybe a dimension for your date. Sample data would help in making some of those determinations.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thanks Gerg. Sorry unable to attach the actual file as there is no option to upload the file, but i have posted the picture of the tables below.
Hi,
You should place all Excel files (one for each month and with the same structure month on month) in a single folder. Using Query Editor of PowerBI desktop, append data from all Excel files. Create a Calendar Table and build a relatioship from the Date column of your appended dataset to the Date column of the Calendar Table.
We can then write measures to model your data and build visuals.