Monthly Budget to Daily Budget

[hwalsh]

Hello

I have monthly budget data that is loaded against a date which is the last date of each month for the next 5 years. For example, the budget for Jan-23 is loaded against 31/01/2023. I would like this value to be divided by the days within that month and then that value is used for each individual day. So if the monthly total loaded against 31/01/2023 is £620k I would want each of the 31 days in Jan to have a value of £20k.

I have seen this question been asked several times on the platform before and I have gone through the various files on allocating budgets and watched the content that is on the platform already but the suggested solutions are not working for the format of my data. The code Sam McKay has shared previously is as below which doesn’t work on my data. I think this is because I have my budgets loaded against a day rather than just ‘January’.

Daily Plan =
VAR DaysinContext = COUNTROWS( Dates )
VAR DaysinMonth = CALCULATE( COUNTROWS( Dates ), ALL( Dates ), VALUES( Dates[Month & Year] ) )
VAR CurrentMonth = SELECTEDVALUE( Dates[MonthName] )
VAR MonthlyBudgetAmounts = CALCULATE( [Total Budgets], FILTER( ALL( ‘Product Budgets’[MonthName] ) , CurrentMonth = ‘Product Budgets’[MonthName] ) )

RETURN
IF( OR( HASONEVALUE( Dates[Date] ), HASONEVALUE( Dates[Month & Year] ) ),
DIVIDE( DaysinContext, DaysinMonth, 0 ) * MonthlyBudgetAmounts,
[Total Budgets] )

I have attached the PBIX file and a screenshot of the current plan upload which as you can see if loaded against the last day of the month.
Daily Plan.pbix (171.9 KB)

Thank you for your help!

[Harsh]

Hello @hwalsh,

Thank You for posting your query onto the Forum.

In order to apportion the monthly budgets at a daily level. Firstly, you need to remove the relationship between the Dates and Budgets tables. And create a virtual relationship using the “TREATAS()” function. Below are the steps provided to achieve the results -

1). Eliminate a physical relationship -

2). Create a calculated column in a Budgets table in the “Mmm YYYY” format -

MM-YYYY = FORMAT( WSFlowsPlan[Date] , "Mmm YYYY" )

3). Lastly, write a DAX measure as provided below -

Daily Budgets = 
VAR _Monthly_Budgets = 
CALCULATE( SUM( WSFlowsPlan[Value] ) , 
    TREATAS( VALUES( 'Dates'[Month & Year] ) , WSFlowsPlan[Month & Year] ) )

VAR _No_of_Days_In_A_Month = 
CALCULATE( COUNTROWS( 'Dates' ) , 
    ALL( 'Dates' ) , 
    VALUES( 'Dates'[Month & Year] ) )

VAR _Results = 
IF( ISINSCOPE( 'Dates'[Date] ) , 
    DIVIDE(
        _Monthly_Budgets , 
        _No_of_Days_In_A_Month ) , 
    _Monthly_Budgets )

RETURN
_Results

Below is the screenshot of the final results provided -

I’m also attaching the working of the PBIX file for your reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Daily Plan - Harsh.pbix (156.8 KB)

[EnterpriseDNA]

Hello @hwalsh

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

[EnterpriseDNA]

Hello @hwalsh

We’ve noticed that no response was received from you on the post above.

Just following up if the response above help you solve your inquiry.
If it did, please mark his answer as the SOLUTION.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @hwalsh,

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!