Calculate YTD when you have a fixed amount per week

[lekkerbek]

Hi,

Hope someone can point me in the right direction. 

I have a dimtable with staff:

- employee number

- employee name

- standard number of appointments per week

I have a fact tabel with:

- date

- employee

- appointment

I would like to create a visual that shows the number of actual appointments vs the norm. How would you approach this? Below is an example of some data.

Dimtable					End result
Employeenumber	Employeename	Number of appointments per week			Week	Employeename	Actual	Budget	Difference
1	John Doe	15			1	John Doe	3	15	-12
2	Jane Doe	20			1	Jane Doe	2	20	-18
					2	John Doe	6	15	-9
					2	Jane Doe	4	20	-16
Facttable
Date	Employeenumber	Appointment
2-1-2023	1	1			End result cumulative
2-1-2023	1	1			Week	Employeename	Actual	Budget	Difference
2-1-2023	1	1			1	John Doe	3	15	-12
2-1-2023	2	1			1	Jane Doe	2	20	-18
2-1-2023	2	1			2	John Doe	9	30	-21
10-1-2023	1	1			2	Jane Doe	6	40	-34
10-1-2023	1	1
10-1-2023	1	1
10-1-2023	1	1
10-1-2023	1	1
10-1-2023	1	1
10-1-2023	2	1
10-1-2023	2	1
10-1-2023	2	1
10-1-2023	2	1

[lekkerbek]

Hi,

Thank you for your answer, but I doubt this is the solution to my problem? The dimtable has the information per week in general, so it just says you should have 15 appointments per week. How would you translate that to a date, so for example it should be:

2 January 2023: 15 appointments

9 January 2023: 15 appointments

16 January 2023: 15 appointments

etc

The source is an Excelsheet, so I can implement new columns with these dates, but I was wondering if there is a solution in Power BI.

[amitchandak]

@lekkerbek , create common tables Employee Number and Date. Date may join to one table only , Using the common dimension and and measure from both tables create the above visual, it should work

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...