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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate Cumulative Budget based on distinct week by Customer

Hello Folks

 

I'm looking to create a measure to calculate cumulative budget based on distinct week by customer. The data looks something like this:-

 

Cumulative Budget.png

 

The objective is to show table with [CLIENT] || [CUMULATIVE BUDGET] dynamically change when i select the slicer

I manage to create a measure, but somehow it is not showing the desired results.

 

**bleep** Bud Vol = CALCULATE(sum(BUDGET[AMOUNT]),
filter(VALUES(DATES),
DATES[Week]<= MAX(DATES[Week])))

 

Any help, please.

 

5 REPLIES 5
Anonymous
Not applicable

Check the attached file.

 

Best

D

Anonymous
Not applicable

Thanks once again, much appreciated.

I tweaked the DAX according to my report attributes, no value showing up. For more clarification, i'm attaching more details about  my report requirement and data model.Cumulative Budget_2.png

Hope this make more sense. Please see my DAX based on your suggestion 

 

Absolute Cumulative Budget =

var __absoluteMaxYearWeek =

    CALCULATE(

        MAX( BUDGET[Year-Week] ),

        ALL( BUDGET )

    )

var __currentYearWeek = MAX( DATES[Year-Week] )

var __minYearWeek = MIN( DATES[Year-Week])

var __result =

    CALCULATE(

        [Act Vol (Tr)],

        DATES[Year-Week] <= __currentYearWeek,

        ALL( DATES )

    )

var __onlyOneYearVisible = HASONEVALUE(DATES[Year])

var __currentPeriodNotFullyInTheFuture =

    __minYearWeek <= __absoluteMaxYearWeek

var __shouldCalc =

    and(

        __onlyOneYearVisible,

        __currentPeriodNotFullyInTheFuture

    )

return

    if( __shouldCalc, __result )

 

Anonymous
Not applicable

Well, you have to adjust it. Year-Week is something that must be correctly sorted. You cannot have 2020-1 because such strings will not sort correctly. You can instead use something that will number all weeks across all years. The sequence of weeks is important.

You should have revealed your model at the beginning.

Best
D
Anonymous
Not applicable

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

To calculate some running total, you have to have a proper date table in the model or at least something that resembles it. And you don't. Bear in mind as well that cumulative means "knowing the order of time of periods." If you select only "week", you can't know which year it belongs to, and therefore you can't calculate a RT.

Best
D
Anonymous
Not applicable

Thanks for reminding me @Anonymous . I've added the dates in the table, please see below the revised datasets. Hope this make sense to get the measure based on Cumulative Budget based on distinct week by Customer 

 

Cumulative Budget_1.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors