Calculate Budget per Week

[Lukas21]

Hello everyone,
I have a dataset with the budget figures per month of a year. For each YYYYMM a budget figure is written down for the month. The budget table is linked to the date table via this YYYYMM field. This separate date table contains all the days of a year, with monthly, weekly and daily fields etc. A field from this date table indicates whether a day is a working day or not. I have used this to create a calculated column in the budget table, which divides the budget for a month by the working days and thus outputs the budget per day. Now I would also like to determine the budget per week so that the same weekly budget is displayed for each day of a calendar week.
Thanks for any help, I have not found a solution.

LG Lukas

YYYYMM	monthly Budget	daily Budget
202401	1000	45,45
202402	2000	85,24
202403	3000	150
202404	2000	95,24

YYYYMM	Year	Month	Week	Working days
202401	2024	01	01	4
202401	2024	01	02	5
202401	2024	01	03	5
202401	2024	01	04	5
202401	2024	01	05	3
202402	2024	02	05	2

[Anonymous]

Hi @Lukas21 ,

What's your expected result base on your sample data? Could you please explain the logic with the special examples and screenshot? It would be helpful to find the solution. Thank you.

In addition, the following link is the one which has the similar requirement as yours.

Solved: Convert monthly budget to weekly budget - Microsoft Fabric Community

Best Regards

[Lukas21]

Hey @Anonymous,

Thank you for your answer. I would expect that since the first calendar week in 2024 has only 4 working days, as the first of January is a public holiday, that the 45.45 daily budget for January is multiplied by the 4 working days, so for each day of week 1, 181.8 is entered. For week 2, as this week has 5 working days, 45.45 is multiplied by 5 and 227.25 is entered. Is this understandable?

[Anonymous]

Hi @Lukas21 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Best Regards

[Lukas21]

Thank you for the effort, we are getting closer. If I now include the daily hierarchy in the table, the daily budget is shown per daily line, the weekly budget should be fixed here and not just added up in the totals line. There are also special cases such as the 5th calendar week, which is made up of 3 working days in January and 2 in February. The total is calculated correctly in your illustration, but if you filter by week 5 only, you will see that the total is calculated incorrectly for the entire week.

Thanks for the help