Data Modeling

[Dnzcn]

Hi everyone,

I have two historical tables and I am converting these tables to day by day with dimdate table using sql storedprocedure.  My aim is to report in PowerBI the leave information of the employees according to the work place on the specified date or date range using a date filter.

What I want to ask is that

-it takes a long time to convert tables day by day in sql. Is there any other way besides this method ? 

-How should a model be built in such a historical structure, is the day-to-day conversion method correct ?

(I wrote the record examples and the number of records in the tables before and after the conversion below. )

Employee Absence Table (885.000 records)

EMPOYEEID	ABSANCE_CODE	STARTDATE	ENDATE
66936	03	2018-01-07 00:00:00.000	2018-01-10 00:00:00.000
66936	01	2018-02-01 00:00:00.000	2018-02-05 00:00:00.000

Employee Assignment Records (87.000 records)

EMPOYEEID	DEPARTMAN_CODE	STARTDATE	ENDDATE
66936	3CCEC2	2018-01-26 00:00:00.000	2018-02-27 00:00:00.000
66936	3CCHA2	2018-01-01 00:00:00.000	2018-01-25 00:00:00.000

Day by Day Workplace (34.000.000 records)

DATE	DEPARTMAN_CODE	EMPLOYEEID		DATE	DEPARTMAN_CODE	EMPLOYEEID
1.01.2018	3CCHA2	66936		1.02.2018	3CCEC2	66936
2.01.2018	3CCHA2	66936		2.02.2018	3CCEC2	66936
3.01.2018	3CCHA2	66936		3.02.2018	3CCEC2	66936
4.01.2018	3CCHA2	66936		4.02.2018	3CCEC2	66936
5.01.2018	3CCHA2	66936		5.02.2018	3CCEC2	66936
6.01.2018	3CCHA2	66936		6.02.2018	3CCEC2	66936
7.01.2018	3CCHA2	66936		7.02.2018	3CCEC2	66936
8.01.2018	3CCHA2	66936		8.02.2018	3CCEC2	66936
9.01.2018	3CCHA2	66936		9.02.2018	3CCEC2	66936
10.01.2018	3CCHA2	66936		10.02.2018	3CCEC2	66936
11.01.2018	3CCHA2	66936		11.02.2018	3CCEC2	66936
12.01.2018	3CCHA2	66936		12.02.2018	3CCEC2	66936
13.01.2018	3CCHA2	66936		13.02.2018	3CCEC2	66936
14.01.2018	3CCHA2	66936		14.02.2018	3CCEC2	66936
15.01.2018	3CCHA2	66936		15.02.2018	3CCEC2	66936
16.01.2018	3CCHA2	66936		16.02.2018	3CCEC2	66936
17.01.2018	3CCHA2	66936		17.02.2018	3CCEC2	66936
18.01.2018	3CCHA2	66936		18.02.2018	3CCEC2	66936
19.01.2018	3CCHA2	66936		19.02.2018	3CCEC2	66936
20.01.2018	3CCHA2	66936		20.02.2018	3CCEC2	66936
21.01.2018	3CCHA2	66936		21.02.2018	3CCEC2	66936
22.01.2018	3CCHA2	66936		22.02.2018	3CCEC2	66936
23.01.2018	3CCHA2	66936		23.02.2018	3CCEC2	66936
24.01.2018	3CCHA2	66936		24.02.2018	3CCEC2	66936
25.01.2018	3CCHA2	66936		25.02.2018	3CCEC2	66936
26.01.2018	3CCEC2	66936		26.02.2018	3CCEC2	66936
27.01.2018	3CCEC2	66936		27.02.2018	3CCEC2	66936
28.01.2018	3CCEC2	66936		28.02.2018	3CCEC2	66936
29.01.2018	3CCEC2	66936
30.01.2018	3CCEC2	66936
31.01.2018	3CCEC2	66936

Day By Day Absence Table (6.000.000 records)

DATE	ABSANCE_CODE	EMPLOYEEID
7.01.2018	03	66936
8.01.2018	03	66936
9.01.2018	03	66936
10.01.2018	03	66936
1.02.2018	01	66936
2.02.2018	01	66936
3.02.2018	01	66936
4.02.2018	01	66936
5.02.2018	01	66936

[v-henryk-mstf]

Hi @Dnzcn ,

Whether the advice given by @amitchandak  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[amitchandak · Accepted solution]

@Dnzcn , You can follow table or measure way depending on need

Between Dates - Dates between
Tables
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...