Forum Discussion
Previous Week Calculations
Hi Guys,
I am so stuck here, would really appreciate help
I have daily customer account cancellation (called Vol) data. I want to calculate the sum of previous weeks cancelations along with current week cancelations. for the previous week vol calculation I am using the following measure:
Vol For Prev Week = CALCULATE(
SUM('Activity Table'[Vol]),
(Filter(ALLSELECTED('Activity Table'),'Activity Table'[Week]=max('Activity Table'[Week])-1)))
I then use "Table" visual to show the results (for last 5 weeks). This is how it shows:
As you can see, I have additionally put 'District' column, so that i can see cancelations district wise. Here comes the problem.
the above-mentioned measure shows me the total of all the districts for the previous weeks, whereas i want district wise cancellation numbers.
How do i get this done
Best
Fa
OK, this needs a little explaination. See some sample data I created based on your input (Screen Shot 1).
The same formula works based on FACT Vol values with dates, cities, Disctricts, etc. (Screen shot 3) ** BUT ** you can't summarize AT2_PW_VOL_2 b/c it's a GROUP calcualtion by Week already...
AT2_PW_VOI_2 = CALCULATE(SUM('Activity Table 2'[VOL]), FILTER(ALLSELECTED('Activity Table 2'), 'Activity Table 2'[Calc_Week] = (EARLIER('Activity Table 2'[Calc_Week]) - 1) && 'Activity Table 2'[District] = EARLIER('Activity Table 2'[District])))
Now the 'why' it works... See my 2nd screen shot where i Un-Sumed VOL. Even though '-43' is the correct SUM'ED Total for VOL for the previous week for ON WWW, -43 isn't a SUM'ed value it's really -43 OVER AND OVER AND OVER again for the whole week. As such, 'SUM'ing this column again throws off your numbers.
Side note! I'm assuming you will want to do some form of Delta or % change calcuation. But remember these are columns and not measures, so you have to use something like MAX or AVG with the AT2_PW_VOL_2 column not to throw off your numbers again. Since EVERY entry in AT2_PW_VOL_2 for the week (by district) is the same, it really doesn't matter what you use when creating your follow-up Measures. (AVG might be safer actually?)
DELTA = SUM('Activity Table 2'[VOL]) - MAX('Activity Table 2'[AT2_PW_VOI_2])
Hope this helps!
FOrrest
P.S. A little ISBLANK logic on DELTA will keep your week 1 from looking wrong. :-)
9 Replies
This is a Custom Column (not measure) to display prior week && (and) break up the Districts.
FOrrest
PW_VOI_2 = CALCULATE(SUM('Activity Table'[VOI]), FILTER(ALLSELECTED('Activity Table'), 'Activity Table'[Week] = (EARLIER('Activity Table'[Week]) - 1) && 'Activity Table'[District] = EARLIER('Activity Table'[District])))
Hi Fhill,
many many thanks,
i used this approach earlier, but instead using && i just added another FILTER expression.
Anyhow, it still seems to have a problem, when i created a custom column using :
Column = calculate (
sum('Activity Table'[Vol]),
filter(allselected('Activity Table'),'Activity Table'[Week]= earlier('Activity Table'[Week]) &&
'Activity Table'[District] = EARLIER('Activity Table'[District])))the result is apparently showing my the sum of vol for the same week, not previous,
see below :
what am i doing wrong here?
Thanks
Make sure you include the 'Minus 1' ( Earlier.... -1 ) part to go back a week. If this still doesn't work, can you post a sample of your raw data? It could be a problem that VOI is hard coded for me, but i'm guessing your VOI is already a SUM value of VOI records?
FOrrest
