Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
RD_PowerBI24
Helper I
Helper I

Monthly Budget to Working Days

Dear All, 

 

I am looking to divide my monthly budget into a working day budget. I have the following columns with a cumulative work days & the # of working days in the budget table.

 

I've then attempted to do a MTD sales per cumulative day, by dividing the sales by # of working days (from Budget Table) and then multiply by the # of cumulative days to have a daily budget. This works in the table on the right but when combining with sales in the main graph, I get a very strange summised line, instead of 2 lines close to each other. Please can you help with this? 

 

Sales vs Budget.png

1 ACCEPTED SOLUTION

I don't think you fully understand the proposed solution. You have Budget data at a MONTHLY grain like this:

Month       Budget      Working Days

====        =====     =========

1               22,000        22

2               21,000        21

 

And you have Dates like this:

Month     Date    Working Day

====      ====  ========

1             1/1       0

1             1/2       1

1             1/3       1

 

You need to JOIN the two tables on Month (and Year):

Month     Budget     Date     Working Day      Working Days

=====    =====   ====   =======         =========

1               22,000     1/1       0                         22

1               22,000     1/2       1                         22

...

2               21,000     2/1       1                         21

 

Now create a calculated column in this table:

[Daily Budget] = [Monthly Budget] / [Working Days] * [Working Day]

You don't need SUM here! If using this sample data, the [Daily Budget should come out to 1,000 per working day, and 0 for non-working days.

 

Now the MTD calculation uses the SUM function:

[MTD Budget] = TOTALMTD ( SUM ( [Daily Budget] ), 'Date Dimension'[Date] ).




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





View solution in original post

9 REPLIES 9
ToddChitt
Super User
Super User

We've all been there!

Homer DOOH.jpg




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





ToddChitt
Super User
Super User

What I have done in the past is to join the Budget table to the Dates table on the Month and Year. That will essentially 'blow up' the Budget table to have a Budget row for every day. But that budget number is the MONTHLY budget, not the Daily Budget. As stated before, the formula for [Daily Budget] = [Month Budget] / [Working Days] * [Working Day]. That will give you a 1/20th (or there abouts) budget value for each working day, but because you multiply it by the Working Day flag, (1 or 0) the weekends get $0 budget.

You need the data at this daily granularity.

Now create a calculation for MTD Budget = TOTALMTD( [Daily Budget], 'Dates'[Date] ) and line plot that on the Y axis against the Date on the X axis.

A couple of things:

Your Date dimension should have all dates, which it looks like it does.

You do not need Cumulative Working Days. 

When you plot it, you should see plateaus at the non-working weekends.

 

Hope that helps.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Hi ToddChitt,

 

Thanks for the info.

I tried this with 

Daily Budget = (SUM('UK Monthly Budget'[Budget FY24])/SUM('UK Monthly Budget'[Working Days]))
& then 
MTDBudget = TOTALMTD(SUM('UK Monthly Budget'[Daily Budget]),'Working Day'[Invoice Date])
 
However, the sum function doesn't work. (Changed format to Number) I get the same value for all dates and now sum when in a graph:
 
 
 
RD_PowerBI24_0-1712068050467.pngRD_PowerBI24_1-1712068095145.png

 

I don't think you fully understand the proposed solution. You have Budget data at a MONTHLY grain like this:

Month       Budget      Working Days

====        =====     =========

1               22,000        22

2               21,000        21

 

And you have Dates like this:

Month     Date    Working Day

====      ====  ========

1             1/1       0

1             1/2       1

1             1/3       1

 

You need to JOIN the two tables on Month (and Year):

Month     Budget     Date     Working Day      Working Days

=====    =====   ====   =======         =========

1               22,000     1/1       0                         22

1               22,000     1/2       1                         22

...

2               21,000     2/1       1                         21

 

Now create a calculated column in this table:

[Daily Budget] = [Monthly Budget] / [Working Days] * [Working Day]

You don't need SUM here! If using this sample data, the [Daily Budget should come out to 1,000 per working day, and 0 for non-working days.

 

Now the MTD calculation uses the SUM function:

[MTD Budget] = TOTALMTD ( SUM ( [Daily Budget] ), 'Date Dimension'[Date] ).




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Hi ToddChitt, thanks for the quick response. I understood what you meant now and created this. Yes you're correct, it blows up the new budget table into a daily budget figure for each row

 

The daily budget is as :

 

Daily Budget = Budget_WorkingDay[Budget FY24]/Budget_WorkingDay[Working Days]* Budget_WorkingDay[Working Day.Working Day]

 

The MTD Budget is: 

MTD Budget = TOTALMTD(SUM(Budget_WorkingDay[Daily Budget]),Budget_WorkingDay[Working Day.Invoice Date])
 
However the MTD Budget is not a rolling number and I get the same numbers Daily and MTD:
RD_PowerBI24_0-1712682584013.png

 

Am I missing something here?

This may seem like an obvious question but I have to ask anyway:

Did you create the MTD Budget as a MEASURE, or as a Calculated Column? It needs to be a Measure so it can operate on the entire table. 

 




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Ah silly me. I made it as a calculated column instead of measure. It works now! Thank you for your patience

RD_PowerBI24
Helper I
Helper I

Hi, this is my working day table and budget table:

 

Working Day Table

Invoice DateWeekdayWorking DayCumulative DaysFinancial Year
01/03/2024511FY24 - Q3
02/03/2024601FY24 - Q3
03/03/2024701FY24 - Q3
04/03/2024112FY24 - Q3
05/03/2024213FY24 - Q3
06/03/2024314FY24 - Q3
07/03/2024415FY24 - Q3
08/03/2024516FY24 - Q3
09/03/2024606FY24 - Q3
10/03/2024706FY24 - Q3
11/03/2024117FY24 - Q3
12/03/2024218FY24 - Q3
13/03/2024319FY24 - Q3
14/03/20244110FY24 - Q3
15/03/20245111FY24 - Q3
16/03/20246011FY24 - Q3
17/03/20247011FY24 - Q3
18/03/20241112FY24 - Q3
19/03/20242113FY24 - Q3
20/03/20243114FY24 - Q3
21/03/20244115FY24 - Q3
22/03/20245116FY24 - Q3
23/03/20246016FY24 - Q3
24/03/20247016FY24 - Q3
25/03/20241117FY24 - Q3
26/03/20242118FY24 - Q3
27/03/20243119FY24 - Q3
28/03/20244120FY24 - Q3
29/03/20245020FY24 - Q3
30/03/20246020FY24 - Q3
31/03/20247020FY24 - Q3

 

 

Budget Table:

ProductMonthBudget FY24Financial YearWorking Days
AJul-23          269,372FY24 - Q121
AAug-23         283,996FY24 - Q122
ASep-23         264,235FY24 - Q121
AOct-23         289,872FY24 - Q222
ANov-23         296,478FY24 - Q222
ADec-23          269,559FY24 - Q219
AJan-24          279,327FY24 - Q322
AFeb-24          286,271FY24 - Q321
AMar-24          296,318FY24 - Q320
AApr-24         284,535FY24 - Q422
AMay-24         328,459FY24 - Q421
AJun-24           319,776FY24 - Q421

 

I have the working day table that goes up to end of 2026. 

 

My formula is below, but I believe it is inflating all the Cumulative day values by summing and multiplying by the monthly budget for each day of the month. Any Powerquery to get around this would be great, as I have added the Working Days in the manual file instead would be great to avoid this inflation.

 

MTD Budget = (SUM('UK Monthly Budget'[Budget FY24])/SUM('UK Monthly Budget'[Working Days]))*SUM('Working Day'[Cumulative Days])

ToddChitt
Super User
Super User

Can you supply a sample of the raw data, NOT as summarized in a table visual?

For breaking up a monthly budget into its day component, sounds like you  did the math right. But raw data would help.

Daily Budget for the month = Monthly Budget divided by the number of working days in the month.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.