Determine Weekly Budget on Daily Level and accumulate

[joshua1990]

Hello everyone!

We have a budget table like this:

Area	Budget	Year-Week
A	5000	2020-05

Now I would like to divide the weekly budgets by 5 to get the daily budget.

Then I would like get a running total on a daily level.

How is that possible?

I simple DIVIDE is not working.

What did I forget?

[Anonymous]

That's easy. Instead of having a table with weekly budgets, create a table (in PQ or DAX) with day by day allocation. Then, the sum on the week level will be the same as the original table but the advantage is that you'll also have your budgets allocated to each and every day of the week.

Best
D

[amitchandak]

@joshua1990 , you want to create a daily allocation table?

You can check this file. Not from the week. But the idea is the same.

https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

Why are you not able to divide?

For Week calendar and week related stuff refer these two.

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

[Greg_Deckler]

@joshua1990 - Hmm, well here is a measure with a similar goal:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111

That being said, seems like you might need to use something like GENERATESERIES or something to generate a daily table. Or create a Calendar table with all of your individual days and then create a new column and essentially look up the week in your budget table, get the budget, divide by 5.