Turning a monthly budget into a daily budget for a table

[JJ2022PBI]

Hello

I have a budget data in Power BI that is input on the last day of the month but is for the whole of that month. I would like to show this by day in equal installments. For example if the monthly budget entered on 31/01/2023 is £620k I would like to convert this to a daily amount on each day of that month of £20k, with the total daily amount coming to £620k for Jan-23.

I have looked at similar messages on this forum but the suggested solutions don't work for me.

I currently have this formula but it only divides the monthly budget by the number of days int hat month, rather than having a value for each day so that the totals for the month match.

Plan Daily WS Flows = sum(WSPlan[Value])/day(EOMONTH(max(WSPlan[Date]),0))

I also have a dates table called 'Dates' with the column date being in the date.

Thank you for your help in advance.

[ichavarria]

Hi @JJ2022PBI,

I think one way you can solve this is by creating a new measure similar to this (You may need to change a couple of things depending on your data):

Daily Budget = DIVIDE(SUM(WSPlan[Value]), DAY(EOMONTH(MAX(WSPlan[Date]),0)))

This formula uses the DIVIDE function to divide the total budget value for the month (calculated using SUM(WSPlan[Value])) by the number of days in the month (calculated using DAY(EOMONTH(MAX(WSPlan[Date]),0))).

Once you have this measure, you can use it in your visuals to display the daily budget amounts for each day of the month. To do this, you can add the Dates table to your visual, and then add the Daily Budget measure to the values section of the visual.

You should also add a filter to your visual to only show the data for the month you're interested in (e.g., January 2023). This will ensure that the daily budget amounts are only displayed for the relevant time period.

Let me know if this works!

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

[JJ2022PBI]

Thanks @ichavarria 

This method still only shows me one day, rather than all the days in the month. When Jan-23 is selected in the slicer is it possible to show all the days in the month each with a daily value in them? 

Plan WS Flows = the plan at monthly level (currently all input on the last day of the month)

Plan Daily 2 WS Flows = the code you shared above that should have a value for each day in Jan

Thanks again for your help.

[JJ2022PBI]

Hi @ichavarria. I have done some digging and found this DAX formula, it doesn't work for me as my budget is loaded for the whole month on the last day of the month rather than by entering '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] )