Cumulative Budget - New Month

DAX DAX Calculations
1

Hi Fellow Power BI Users -

Would you know what is causing the behavior that is jumping the cumulative Budget $$ when a new Month starts? Thanks

CumulativeBudget$:= CALCULATE([Budget Allocation],
                          FILTER(ALLSELECTED(Dim_Date),
                          Dim_Date[FullDate] <= Max(Dim_Date[FullDate])))

BudgetAllocationJump
BudgetAllocationJump.JPG612×574 74.2 KB

2

Hi Sam, I was going to delete my post as I found what I was looking for in the “Advanced Budgeting Insights” module at around the 38 minute mark. :slight_smile:
I left it here in case anyone else stumbled across my post.
Thanks

3

Ok great, I presume it was this technique that solved it

2 Likes
4

Is it possible to remove ‘workdays’ from this measure? I’ve attached a sample copy of my PBI for reference. I remove the ‘workdays’ within the allocation measure, but the cumulative measure does not, so the end result equals more then expected.

For example, in the image below, the Cumulative Budget Hours test should be 1,330.02 for January but totals 1,995.03.

image
image1276×419 31.3 KB

Sample Data.pbix (588.1 KB)

5

Hello @Jackie,

Thank You for posting your query onto the Forum.

Well the formula which you’re referring in the screenshot is not available in the file that you’ve posted. We can see the below provided formula in your file -

Cumulative Budget Hours = 
IF( [Billable Hours] = BLANK(), BLANK(),
    CALCULATE( [Budget Hour Allocation] ,
	    FILTER( ALLSELECTED( Dates ),
		Dates[Date] <= MAX( Dates[Date] ) ) ) )

And since the context of the Budget is different than the normal scenario you cannot use the normal “Cumulative Formula” to calculate the Cumulative Budgeted Hours. Below is the formula provided for the reference that will be required to be used for calculating the Cumulative Budgeted Hours to achieve the results -

Cumulative Budget Hours - Harsh = 
VAR _Cumulative_Budget_Hours = 
CALCULATE( 
    SUMX( 
        SUMMARIZE( 
            Dates , 
            Dates[Date] , 
            "Budgets" , 
            [Budget Hour Allocation] ) , 
        [Budgets] ) ,
	FILTER( ALLSELECTED( Dates ) ,
		Dates[Date] <= MAX( Dates[Date] ) ) )

RETURN
IF( ISBLANK( [Billable Hours] ) , 
    BLANK() , 
    _Cumulative_Budget_Hours )

Now, if you observe the results, you’ll see that “Cumulative Budgeted Hours” for Jan’2021 is 1,330.02 and not some other results that you were getting in your file.

Results
Results1920×1080 263 KB

I’m also attaching the working of the PBIX file for the reference as well providing the link of the article from the blogpost as well as of video which Sam Sir has created pertaining to this very specific topic which addresses this type of issue in-depth.

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

Please Note: Posting onto the solved thread reduces it’s visibility. You can refer the old solved threads for the reference purposes but always start a new query by creating a new post/thread onto the Forum so that members can serve you in a better and efficient manner.

Looping in here @EnterpriseDNA team so that they can create a new thread of this query.

Thanks and Warm Regards,
Harsh

Sample Budgeted Data - Harsh.pbix (578.8 KB)

2 Likes
6

@Harsh, thank you. I must have made the changes to the PBI file after I attached it. My ‘blanks’ dax was incorrect and with your assistance, it is now working as expected.

Thank you for your time!

7

Hello @Jackie,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you and you found the solution helpful in your scenario.

Thanks and Warm Regards,
Harsh