Calculate Cumulative Budget based on distinct week by Customer

[Anonymous]

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:-

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.

[Anonymous]

Check the attached file.

Best

D

[Anonymous]

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.

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]

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]

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]

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