Previous Week Calculations

[fahadakbar]

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 

[fhill]

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])))

[fahadakbar]

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

[fhill]

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

[fahadakbar]

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 · Accepted solution]

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.  🙂

[fahadakbar]

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?

[fhill]

Yea, I wish PowerBI / DAX would use a different word that 'Earlier', it's confusing... You can acctually use EARLIER to look up LATER values...  This Blog by TinyLizard explains it in depth...

http://tinylizard.com/dax-earlier-function/

Use my Delta formula as a base to add some ( ) 's and a divide by AVERAGE( PW VOI..) again to produce your % change calcualtion.    (Solution below the screen shot if you get stuck).    🙂   FOrrest

DELTA% = IF(ISBLANK(AVERAGE('Activity Table 2'[AT2_PW_VOI_2])),0, SUM('Activity Table 2'[VOL]) - AVERAGE('Activity Table 2'[AT2_PW_VOI_2])) / AVERAGE('Activity Table 2'[AT2_PW_VOI_2])

[fahadakbar]

Just taking benefit from your in-depth knowledge here, why can't the same thing be achieved from Measures? why do we need to create columns?

[fhill]

There are ways to create measures to do the same thing, Google DAX EARLIER AS a Measure for examples.  Everyone's programming style is different; no right or wrong way to do things, just how I perfer to go at a solution.