Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Calculate YTD when you have a fixed amount per week

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
2 REPLIES 2
Helper IV

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.

Super User

@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...

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors