How to create Monthly Allocation

DAX DAX Calculations
1

In the link below they show how to calculate monthly allocation based on daily budgeting.
Budget Allocation: Monthly Forecasts Across Daily Results In Power BI | Enterprise DNA

What I want is the allocation from year to monthly wise not by daily allocation. Do you any idea how to do the allocation.

I try to use this calculation but it doesn’t work.

Monthly allocation =
Divide ( values ( calendar[ monthnum]), max( calendar [monthnum]))

2

Hello @Amirul,

Thank You for posting your query onto the Forum.

You can convert your “Yearly” budgets into the “Monthly” as well as “Daily” aspects. Below is the formula provided for the reference (Note: The formula is in different context so adjust that accordingly as well adjust the naming conventions as per your file)

Budget Allocation 1 = 
IF( OR( 
    HASONEVALUE( Dates[Date] ) , 
    HASONEVALUE( Dates[Month & Year] ) ) ,
	    DIVIDE( 
            COUNTROWS( Dates ) , 
            CALCULATE( COUNTROWS( Dates ) , 
                ALL( Dates ) , 
                VALUES( Dates[Month & Year] ) ) , 0  ) * 
            CALCULATE( [Total Budgets] , 
                FILTER( ALL( 'Product Budgets'[Month Name] ) , 
                SELECTEDVALUE( Dates[Month Name] ) = 'Product Budgets'[Month Name] ) ) , 
            [Total Budgets] )

I’m also attaching the working of the PBIX file for the reference. There’re couple of alternative formulas as well into the file you can check out that as well. And also providing few of the links of the videos for the reference purpose. Also you can go through the course on “Budgeting and Forecasting” available onto our education portal.

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

Thanks and Warm Regards,
Harsh

Budgeting - Harsh.pbix (583.0 KB)

2 Likes