Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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 )
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
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |