Forum Discussion
Previous Week Calculations
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. :-)
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
Resident RockstarMake 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
Yes,
i am including Earlier in the DAX expression for both(district & week)
you are right, VOL is already a sum of week
(Measure)
Vol total for Week =
CALCULATE(SUM('Activity Table'[Vol]), ALL('Activity Table'[Week]))Originally, data sate is like this :
Date, Week ,Prov, District, City , VOL
1/1/2017 ,1, ON , XXX, YYY , -1
1/2/2017 ,1, PE, CCC, AAA. -1
** week is also a calculated number through weeknumber(date)
- fhill
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. :-)
Awesome !
such a great learning ,
in fact, i was missing Minus 1 thing, i didn't notice that it has to be like that :
filter(allselected('Activity Table'),'Activity Table'[Week]= (earlier('Activity Table'[Week])-1)
Question: why do I have to use Minus one, and why 'Earlier' alone is not sufficient?
Your Side note is also correct, my aim is to grab the percentage difference between current week average and last week average (by district)
so how do I get that?