Create daily budget based on monthly Budget split by Day

[obiee1]

Hello Everyone,

This is the first time I'm asking a question and would like to get some help a situation that I can't seem to get out of. I have a date table, and a budget table. The budget table looks something like this 

State Category Month  Day           Amount

XXX   abc          July      Wednesday $500

XXX    abc          Aug      Monday    $600

XXX   abc          July     Thursday     $1000

XXX   xyz          July     Monday      $100

ZZZ   abc          July     Monday     $700

Date table

Date Month  Year   Day Name   Day   Week   Month Name

01/07/2020   2020  Wednesday  1        26       July

02/07/2020   2020  Thursday      2        26       July

03/07/2020   2020  Friday           3        26       July

.......

What I'm trying to do is to split the budget number split across days but it should be able to take into account the number of Mondays in month and in a week to calculate to calculate target on daily basis

so If the selected day is just 01/07/2020 for category abc for state XXX then result should be $100 as there are 5 Wednesdays in the month of July

If the date selection is 01/07/2020 to 02/07/2020 for category abc for state XXX then result should be $300.

Os is there a way that I can create a new table based on the 2 tables I have where the budget gets split in the new table based on the number of Mondays , Tuesdays , Wednesdays in a month and place the value against each date for each state and catergory?

Any help is greatly appreciated.

[v-easonf-msft]

Hi , @obiee1 

Do you have resolved it? For now, there is no content of description in the thread.

If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi , @obiee1 

The column "week " seem to be wrong  in your "Date Table"  .

The weekbum should be 27 rather than 26

It is recommended  to create a specific date column that meets the requirements in budget table  and  a  date column in DateTable. So that you can select related date columns to  create a relationship between  two tables. 
About how to construct the date columns in two tables, please check the samlple file.

pbix attached

Then you can create measure as below:

Measure = 
SELECTEDVALUE('Budget Table'[Amount])/SELECTEDVALUE('Calendar Table'[num_of_weekday_in_same_month])

The result will show as below:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[obiee1]

Is there a way I can do a cross join the Budget and Date table to create a new table, and split the budget across each date ? so if the budget is $500 for 5 wednesdays in the month of July for state XXX and category abc, it will place $100 against each Wednesday for the month of July, and for Mondays in July for category abc for state ZZZ it will say ($700/4) = $175 for each date.