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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Cumulative Budget Allocation Formula With Mismatched Granularities

Hi, 

I'm currently working on a measure that calculates the cumulative budget allocation with mismatched granularities.

I used this Blog by Enterprise DNA to get the formulations of my measures.

Also important to note that I've described my problem in more detail in my previous post but the solution created a different issue for me.

This is how my data is set up:

PBI Help 11.PNG

 

This is the problem I'm running into:

PBI Help 13.PNG

 

[BudgetAllocation] is fine if I wanted the individual days, but I want it cumulatively.

[BudgetAllocation2] was my attempt to make it cumulative, but it really just produced the entire value for the month.

[BudgetAllocation3] was another attempt to make it cumulative but because I have the YearMonth = YearMonth, it brings the sum back to zero at the start of each month. 

 

My 3 measures are as follows:

BudgetAllocation =
VAR DayInContext = COUNTROWS('Dates')
VAR DaysInMonth = CALCULATE( COUNTROWS( 'Dates' ), ALL( 'Dates' ),
VALUES( 'Dates'[Year Month] ) )
VAR CurrentMonth = SELECTEDVALUE( 'Dates'[Year Month] )
VAR MonthlyBudgetAmounts = CALCULATE( [NonlinearBudgetAmt],
TREATAS( VALUES ( Dates[Year Month] ), 'Nonlinear Budget Table'[YearMonth] ) )

RETURN
IF ( OR( HASONEVALUE( 'Dates'[Date] ), HASONEVALUE ( 'Dates'[Month Year] ) ),
DIVIDE ( DayInContext, DaysInMonth, 0 ) * MonthlyBudgetAmounts, [NonlinearBudget])
 
BudgetAllocation 2 =
VAR MaxYearMonth = MAX('Dates'[Year Month])
VAR DayInContext = CALCULATE(COUNTROWS('Dates'),
ALL('Dates'),'Dates'[Year Month] = MaxYearMonth )
VAR DaysInMonth = CALCULATE( COUNTROWS( 'Dates' ),
ALL( 'Dates' ), VALUES( 'Dates'[Month Year] ) )
VAR CurrentMonth = SELECTEDVALUE( 'Dates'[Year Month] )
VAR MonthlyBudgetAmounts = CALCULATE( [NonlinearBudgetAmt],
TREATAS( VALUES ( Dates[Year Month] ), 'Nonlinear Budget Table'[YearMonth] ) )

RETURN 
IF ( OR( HASONEVALUE( 'Dates'[Date] ), HASONEVALUE ( 'Dates'[Month Year] ) ),
DIVIDE ( DayInContext, DaysInMonth, 0 ) * MonthlyBudgetAmounts, [NonlinearBudgetAmt])
 
 
BudgetAllocation 3 =
VAR MaxDayMonth = MAX('Dates'[Date])
VAR MaxYearMonth = MAX('Dates'[Year Month])
VAR AllDates = ALL(Dates)
VAR DayInContext = CALCULATE(COUNTROWS('Dates'),
ALL('Dates'),'Dates'[Year Month] = MaxYearMonth && 'Dates'[Date] <= MaxDayMonth )
VAR DaysInMonth = CALCULATE( COUNTROWS( 'Dates' ),
ALL( 'Dates' ), VALUES( 'Dates'[Month Year] ) )
VAR CurrentMonth = SELECTEDVALUE( 'Dates'[Year Month] )
VAR MonthlyBudgetAmounts = CALCULATE( [PossSol2],
TREATAS( VALUES ( Dates[Year Month] ), 'Nonlinear Budget Table'[YearMonth] ) )
RETURN
IF ( OR( HASONEVALUE( 'Dates'[Date] ), HASONEVALUE ( 'Dates'[Month Year] ) ),
DIVIDE ( DayInContext, DaysInMonth, 0 ) * MonthlyBudgetAmounts, [NonlinearBudgetAmt])
 
 
 
 If anyone knows how to make BudgetAllocation cumulative, let me know.
Thanks!
 
 
2 REPLIES 2
Anonymous
Not applicable

Hi @v-tangjie-msft ,

Thank you for your response, but that assumes that the budgets are linear. We can assume that the budget is linear within each day of the month, but each month has a different budget. 

These are roughly the formulations I'm looking for:

 

PBI Help 15.PNG

From my second screenshot in my original post you can see the [BudgetAllocation] measure provides the daily budget for each month (shown as [April Daily Budget] in the screenshot above). [BudgetAllocation 3] does basically what [Cumulative April Daily Budget] does, but doesn't consider the months prior. 

 

DateBudgetAllocationBudgetAllocation 2BudgetAllocation 3CumulativeBudgetAllocation
1-Apr-22655.6812457.87655.68655.68
4-Apr-22655.6812457.871311.361311.36
5-Apr-22655.6812457.871967.031967.04
6-Apr-22655.6812457.872622.712622.72
7-Apr-22655.6812457.873278.393278.4
8-Apr-22655.6812457.873934.073934.08
11-Apr-22655.6812457.874589.744589.76
12-Apr-22655.6812457.875245.425245.44
13-Apr-22655.6812457.875901.15901.12
14-Apr-22655.6812457.876556.786556.8
19-Apr-22655.6812457.877212.457212.48
20-Apr-22655.6812457.877868.137868.16
21-Apr-22655.6812457.878523.818523.84
22-Apr-22655.6812457.879179.499179.52
25-Apr-22655.6812457.879835.169835.2
26-Apr-22655.6812457.8710490.8410490.88
27-Apr-22655.6812457.8711146.5211146.56
28-Apr-22655.6812457.8711802.211802.24
29-Apr-22655.6812457.8712457.8712457.92
2-May-22608.212772.28608.213066.12
3-May-22608.212772.281216.4113674.32
4-May-22608.212772.281824.6114282.52
5-May-22608.212772.282432.8214890.72
6-May-22608.212772.283041.0215498.92
9-May-22608.212772.283649.2216107.12
10-May-22608.212772.284257.4316715.32

 

I'm looking for the [CumulativeBudgetAllocation]. I've highlighted where [BudgetAllocation3] and what I'm looking for start to differ on the first workday of May (2-May-22).

v-tangjie-msft
Community Support
Community Support

Hi @Anonymous , 

 

According to your description, you want to You want to accumulate calculations [Budget]. Here are my steps you can follow as a solution. 

(1)My test data comes from the example data you provided in the previous post. Re: Nonlinear Budget Release Measure - Microsoft Power BI Community 

 

(2)We can create a measure.  

BudgetAllocation =  

var _slice=SELECTEDVALUE('Dates'[DateKey]) 

var _table=FILTER(ALL('Budget Table'),'Budget Table'[DateKey] <= _slice) 

return 

SUMX(_table,'Budget Table'[Budget ]) 

 

 

(3)Then the result is as follows. 

vtangjiemsft_0-1663228598186.png

If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you. 

 

Best Regards, 

Neeko Tang 

If this posthelps, then please considerAccept it as the solutionto help the other members find it more quickly. 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors