cancel
Showing results 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

Frequent Visitor

Expanding monthly budget split by day name into daily budget

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.

1 ACCEPTED SOLUTION
Microsoft Employee

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

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

3 REPLIES 3
Microsoft Employee

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

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Frequent Visitor

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

Resident Rockstar

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

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.