Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I was created cumalative totals with the formula:
total_business(cumulative) =
CALCULATE (
SUM ( business_latest[total_business] ),
FILTER (
ALL ( 'business_latest' ),
'business_latest'[Date since registered] <= MAX( ( 'business_latest'[Date since registered] )
)
))
But my result return like this:
Instead of sum the total up by date(s), but the return result like 708 * Total business, eg:
25/10/2017 total Business 3. cummulative total should be 3, but return as 3x708 = 2124
1/11/2017 total Business 114. cummulative total should be 3+114 = 117, but return as 114x708 = 80712
Anyone know why?
Thanks
Solved! Go to Solution.
This is a sample table with calculated column [weekend_WED]:
weekend_WED = Tabelle1[Date1]-MOD(Tabelle1[Date1]-5;7)+6
and the measure for cumulative:
total(kum) = CALCULATE (SUM(Tabelle1[total]); FILTER(ALL(Tabelle1); Tabelle1[Date1]<=MAX(Tabelle1[Date1])))
Hi @Anonymous,
In your scenario, maybe you need to replace [Date since registered] with [week_end_day (wed) ] in running total measure.
total_business(cumulative) = CALCULATE ( SUM ( business_latest[total_business] ), FILTER ( ALL ( 'business_latest' ), 'business_latest'[week_end_day(wed)] <= MAX( ( 'business_latest'[week_end_day(wed)] ) ) ))
Regards,
Yuliana Gu
The formula also not working from my end. May I know what's wrong
This is the formula I used:
total_business(cumulative) =
CALCULATE (
SUM ( business_latest[total_business] ),
FILTER (
ALL ( 'business_latest' ),
'business_latest'[Date since registered] <= MAX( ( 'business_latest'[Date since registered] )
)
))
Then second formula to sum up my weekly total (ended on Wed)
[week_end_day (wed) ] = 'business_latest'[Date since registered]-MOD(business_latest[Date since registered]-5,7)+6
And this is what I get.
Appreciate your help.
Hi @Anonymous,
In your scenario, maybe you need to replace [Date since registered] with [week_end_day (wed) ] in running total measure.
total_business(cumulative) = CALCULATE ( SUM ( business_latest[total_business] ), FILTER ( ALL ( 'business_latest' ), 'business_latest'[week_end_day(wed)] <= MAX( ( 'business_latest'[week_end_day(wed)] ) ) ))
Regards,
Yuliana Gu
Solved, thanks everyone.
This is a sample table with calculated column [weekend_WED]:
weekend_WED = Tabelle1[Date1]-MOD(Tabelle1[Date1]-5;7)+6
and the measure for cumulative:
total(kum) = CALCULATE (SUM(Tabelle1[total]); FILTER(ALL(Tabelle1); Tabelle1[Date1]<=MAX(Tabelle1[Date1])))
I tired follow what you shown here, but still not working for me, maybe the date format caused the problem?
I recommend making this a measure instead of a calculated column. The below solution provides you with a simplistic solution that may require tweaking to take into account more complex scenarios.
The below doesn't take into account any slicers you may have set up and totals your entire "Total Business" from beginning until the date of context. If you put this measure into a matrix or table visual, with dates as your rows. That will form the basis of the context.
total_business(cumulative) = var dateUntil = LASTDATE('business_latest'[week_end_day (wed)]) RETURN CALCULATE ( SUM('business_latest'[total_business]'), ALL('business_latest'), 'business_latest'[week_end_day (wed)] <= dateUntil )