Expanding monthly budget split by day name into daily budget

[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

Budget

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.

Not sure if a measure or a new table is the correct choice.

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.

Any help is greatly appreciated.

[mahoneypat · Accepted solution]

I didn't have time to try this out but I think this approach will work, doing it all in the query editor.

1. Start with your budget table

2. Merge it with your Date table (created in M obviously).  Make the joins on the Month and Day columns.

3. Before you expand it, add a custom column to count the # of rows in the merged Date table (e.g., # of Wednesdays)

4. Add another column to divide your total amount by the count in #3

5. Expand your Date table to get your result with the Date you need and the divided amount

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[obiee1]

Hi Mahoneypat,

Thank you for the reply, I think this could be a solution. I struggled with pont 3 of yours though. 

If i'm adding it before expanding then would I do it before merge also? or have I not undstood it correctly?

Regards

[sturlaws]

Hi @obiee1 

Do you still have issues with this?

If you've fixed the issue on your own please kindly share your solution. If any of the above posts helped, please kindly mark it as a solution to help others find it more quickly. Thanks!

Cheers,
Sturla